Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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. 

 

 

Labels (2)
1 Solution

Accepted Solutions
TRF
Champion II
Champion II

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.

View solution in original post

8 Replies
TRF
Champion II
Champion II

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.

Anonymous
Not applicable
Author

I will give that a try.

 

Thanks

Anonymous
Not applicable
Author

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

vboppudi
Partner - Creator III
Partner - Creator III

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

 

0683p000009LvBr.png

Regards,

Anonymous
Not applicable
Author

Thanks I will give that  a try.

Anonymous
Not applicable
Author

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
TRF
Champion II
Champion II

Item separator should be "\t" not ",".
Anonymous
Not applicable
Author

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