Derive New Column based on column values from Source

shannoypaul — Tue, 17 Jul 2018 18:18:28 GMT

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

Re: Derive New Column based on column values from Source

lironbaram — Tue, 17 Jul 2018 19:27:06 GMT

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);