Six Stars

Using a Lookup on a comma separated source field

I am new to Talend and I am using version 6.4.0.

 

I have a use case where I need to replace the incoming human readable value with a coded value. The incoming data is comma separated and I have a created a lookup file that has both the human readable value and the code. I have joined the source file with the lookup file and mapped it to the output field I want it to be in, but when I run the job the only values that are replaced are when the source only has a single value in the input field.

 

Example: Source file has French Doors, Hardwood Floors. My lookup table is attached. I would expect that it would go through each value and replace it with the code so for this example the data returned should be FNCH-DR, HARDWOOD but instead nothing is returned.

 

I am sure I have something setup wrong on the join. Any help would be appreciated. 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: Using a Lookup on a comma separated source field

Split the field DinningRoomFeatures (using tNormalize) giving 1 row per item.

Use the result as main input for your tMap to translate each value and finaly use tDenormalize to reconstruct the original records with DinningRoomFeatures field translated.

If the original record don't have a key field, add a sequence (using tMap) for your listing fileand remove when the job finish (using tFilterColumn).

Hope this helps.


TRF
Six Stars

Re: Using a Lookup on a comma separated source field

I figured out my issue and it is working fine now.

8 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Using a Lookup on a comma separated source field

Split the field DinningRoomFeatures (using tNormalize) giving 1 row per item.

Use the result as main input for your tMap to translate each value and finaly use tDenormalize to reconstruct the original records with DinningRoomFeatures field translated.

If the original record don't have a key field, add a sequence (using tMap) for your listing fileand remove when the job finish (using tFilterColumn).

Hope this helps.


TRF
Six Stars

Re: Using a Lookup on a comma separated source field

I will give that a try.

 

Thanks

Six Stars

Re: Using a Lookup on a comma separated source field

Next issue is I never know how many values will come in a given field. How do I handle that?

Six Stars

Re: Using a Lookup on a comma separated source field

Hi,

 

if you are looking for below out put:

 

tarting job job_ReplaceCodes at 17:11 21/07/2017.

[statistics] connecting to socket on port 3496
[statistics] connected
.-----------------------------------------------.
| SOURCE_DATA |
|=---------------------------------------------=|
|DinningDetails |
|=---------------------------------------------=|
|French Doors,Hardwood Floors,Air Cleaner |
|Bathroom |
|Beam Ceilings,Built-in Microwave |
|Compactor,Coved,Dumbwaiter,Eat Bar,French Doors|
'-----------------------------------------------'
.--+-------------------------------------.
|TARGET_DATA|
|=-+------------------------------------=|
|ID|ShortValue |
|=-+------------------------------------=|
|1 |FNCH-DR,HARDWOD,AIRCLEN |
|2 |BATH |
|3 |BEAMS,BI-MICO |
|4 |COMPCTR,COVED,DMBWAIT,EAT-BAR,FNCH-DR|
'--+-------------------------------------'

[statistics] disconnected
Job job_ReplaceCodes ended at 17:11 21/07/2017. [exit code=0]

 

Please try below job

 

ReplaceCodes.PNG

Regards,

Veeranjaneyulu Boppudi
Six Stars

Re: Using a Lookup on a comma separated source field

Thanks I will give that  a try.

Six Stars

Re: Using a Lookup on a comma separated source field

I tried just a the tnormailize for this field and it will only split it up to point and stop.

 

Attached is my job screen shot and sample data.

 

Thanks for the help.

 

Shawn

Twelve Stars TRF
Twelve Stars

Re: Using a Lookup on a comma separated source field

Item separator should be "\t" not ",".

TRF
Six Stars

Re: Using a Lookup on a comma separated source field

I figured out my issue and it is working fine now.