Design a transformation job based on business rules configured in a mapper table.

[DJ10198724]

Hello Group,

A TOS DI newbie here.

My source and destination tables (Both Oracle) have the following fields:

Customer Number, Gender, Nationality, Residing Country

My requirement is to transform the values for Gender, Nationality, Residing Country based on a mapper table which has the following fields.

TransformId | src_val | tgt_val

GENDER | 1 | M

GENDER | 2 | F

GENDER | 3 | O

NATIONALITY| 1 | INDIAN

NATIONALITY| 2 | AUSTRALIAN

NATIONALITY| 3 | AMERICAN

COUNTRY | IN | INDIA

COUNTRY | UK | UK

COUNTRY | US | USA

Could you please help what is the best way to design the job for this?

Thanks!

[jilanisyed]

Hi Edgeverve,

We can implement this multiple ways.

1.you can use tPivotToColumnsDelimited component in Talend. this will generate csv file . create another flow to Load the csv into Oracle.

https://help.talend.com/r/IbcuptEfj4pPTspY7D~KrQ/kcS6t6l90MPVujJosR2OoQ

2.you can use pivot function in select Query to convert your rows to columns.

[gjeremy1617088143 · Accepted solution]

Hi @Daniel J​ ,

you can make for exemple your main input with your data and 3

more input with your mapper table , then in a tmap in each input of the mapper table you select the tfilter expression with for example for the gender : ("GENDER".equals(your column 1 input)

and you make lookup on the value in common with the main input.

Send me Love and kudos

[DJ10198724]

Thanks a lot @guenneguez jeremy​ . This seems to be the only option from whatever information I could muster from other sources. From a performance point of view, is there a better way to do it?

[gjeremy1617088143]

you can read first the mapper table then you filter rows(based on name like GENDER for example) and you stock it into different hash

then you do all the lookup with the hash already filtered.