topic Re: Using a Lookup on a comma separated source field in Talend Studio

Using a Lookup on a comma separated source field

Anonymous — Fri, 21 Jul 2017 20:33:34 GMT

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.

Re: Using a Lookup on a comma separated source field

TRF — Fri, 21 Jul 2017 21:22:57 GMT

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.

Re: Using a Lookup on a comma separated source field

Anonymous — Fri, 21 Jul 2017 21:52:30 GMT

I will give that a try.

Thanks

Re: Using a Lookup on a comma separated source field

Anonymous — Fri, 21 Jul 2017 21:58:15 GMT

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

Re: Using a Lookup on a comma separated source field

vboppudi — Fri, 21 Jul 2017 23:15:52 GMT

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

Regards,

Re: Using a Lookup on a comma separated source field

Anonymous — Sat, 22 Jul 2017 04:54:14 GMT

Thanks I will give that a try.

Re: Using a Lookup on a comma separated source field

Anonymous — Sun, 23 Jul 2017 04:55:17 GMT

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

Screen Shot 2017-07-22 at 10.53.33 PM.png
Sample data.txt

Re: Using a Lookup on a comma separated source field

TRF — Sun, 23 Jul 2017 08:36:21 GMT

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

Re: Using a Lookup on a comma separated source field

Anonymous — Mon, 24 Jul 2017 15:28:03 GMT

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