Derive New Column based on column values from Source

[shannoypaul]

New Column

Customer

Condition 1

If (Market Channet = Direct) then Customer = Account Name

Condition 2

If (Market Channet = Indirect) then Customer = Lookup(Customer_1)

Lookup Key = Account Code + Product Code

Condition 3

If (Market Channet = Hybrid) then Customer = Lookup(Customer_2)

Lookup Key = Product Reference

All Other Cases

Customer = "Not Exist"

Attaching the Case file with the following Sheets:

Source- Source Data to be loaded (transformations to be added while loading this dataset)

Customer_1 and Customer_2 - Mapping (Lookup) Load

[lironbaram · Accepted solution]

hi

this script will

solve your issues

Customer1Map:

mapping LOAD [Account Code] & '_' & [Product Code] as OLD ,

     Customer_1 as New

FROM

(ooxml, embedded labels, table is Customer_1);

Customer2Map:

Mapping LOAD [Product Reference] as Old1,

     Customer_2 as New1

FROM

(ooxml, embedded labels, table is Customer_2);

LOAD [Market Channel],

     [Account Name],

     [Account Code],

     [Product Code],

     [Product Reference],

     if( [Market Channel] = 'Direct',[Account Name],

      if([Market Channel] ='Indirect',ApplyMap('Customer1Map',[Account Code] & '_' & [Product Code],'Not exist'),

      if([Market Channel] ='Hybrid',ApplyMap('Customer2Map',[Product Reference],'Not exist'),'Not exist'))) as Customer

FROM

(ooxml, embedded labels, table is Source);