Replace string from list of values[Lookup table]

Five Stars

Replace string from list of values[Lookup table]

Lookup table

Color CodeColor
PKJTan
PKJBrown
PKJBlack
PKJBlue
BRNBlack
BRNBrown
BRNTaupe

 

 

Input Rows

Product NameColor Code
Prod A1 TanPKJ
Prod A2 Taupe

BRN

 

Expected Output Rows

Product NameColor Code
Prod A1PKJ
Prod A2BRN

 

 

I am trying to use lookup table as Input to tMap with following options
Del.PNG

 

 

 

 

 

 

And in tmap Expression using following expression.

StringHandling.TRIM

(
StringHandling.EREPLACE(row1.ProductName,row8.COLOR,"")
)

 

 

It doesnt work because I am using match model to "unique match". When I switch to "all match" then it populates many rows and eventually throws Disk i/o error because my number of rows increases from 200k to 3m.

 

I would appreciate if someone can help me with a direction/solution.


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Replace string from list of values[Lookup table]

Hi,

You need to split the ProductName before to go to the join.

The simpler is to have a dedicated tMap for this purpose, like this:

Capture.PNG

Based on your sample, here is the result:

Starting job test at 10:02 12/12/2017.

[statistics] connecting to socket on port 3945
[statistics] connected
.-----------+---------+------.
|         tLogRow_22         |
|=----------+---------+-----=|
|productName|colorCode|color |
|=----------+---------+-----=|
|Prod A1    |PKJ      |Tan   |
|Prod A2    |BRN      |Taupe |
'-----------+---------+------'

[statistics] disconnected
Job test ended at 10:02 12/12/2017. [exit code=0]

Now you can go to the tMap with the lookup to replace the color value by the color code issued from your lookup table (using an inner join).

Hope this helps.


TRF

All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Replace string from list of values[Lookup table]

Hi,

You need to split the ProductName before to go to the join.

The simpler is to have a dedicated tMap for this purpose, like this:

Capture.PNG

Based on your sample, here is the result:

Starting job test at 10:02 12/12/2017.

[statistics] connecting to socket on port 3945
[statistics] connected
.-----------+---------+------.
|         tLogRow_22         |
|=----------+---------+-----=|
|productName|colorCode|color |
|=----------+---------+-----=|
|Prod A1    |PKJ      |Tan   |
|Prod A2    |BRN      |Taupe |
'-----------+---------+------'

[statistics] disconnected
Job test ended at 10:02 12/12/2017. [exit code=0]

Now you can go to the tMap with the lookup to replace the color value by the color code issued from your lookup table (using an inner join).

Hope this helps.


TRF

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now