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.

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars TRF
Forteen 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
1 REPLY
Forteen Stars TRF
Forteen 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