Replace string from list of values[Lookup table]

[Anonymous]

Lookup table

Color Code	Color
PKJ	Tan
PKJ	Brown
PKJ	Black
PKJ	Blue
BRN	Black
BRN	Brown
BRN	Taupe

Input Rows

Product Name	Color Code
Prod A1 Tan	PKJ
Prod A2 Taupe	BRN

Expected Output Rows

Product Name	Color Code
Prod A1	PKJ
Prod A2	BRN

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

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.

[TRF · Accepted solution]

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:

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.