Code Explanation

[#1]

Hi All,

Can someone explain the below code.

WBS_Des_MAP:

Mapping

LOAD ID as WBS_Element,

    if(WildMatch([FINAL WBS],'*-'),Replace([FINAL WBS],'-',''),[FINAL WBS]) as WBS_Element_Des

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is DETAILS);

WBS_Des_MAP2:

Mapping

LOAD

    [FINAL WBS],

    F3

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is [GROUP DESCR]);

WBS_Group_Des:

LOAD

    [FINAL WBS] as WBS_Code,

    F3,

    Descr

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is [GROUP DESCR]);

AssignmentToWBSMap:

Mapping

LOAD Co.code,

    if(len(trim([WBS Element]))>0,[WBS Element],

    if(Len(Assignment)>=6,Assignment,H.Text)) as WBS_Code_Final

FROM

[..\10_data_sources\Excel\cashflow-*.csv]

(txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes, no

eof);

LOAD

    'Current' as Cashflow_Period_Type,

    if(ApplyMap('WBS_Des_MAP', [WBS Element],0)<>0, [WBS Element],

    if(ApplyMap('WBS_Des_MAP',Assignment,0)<>0,Assignment,

    if(ApplyMap('WBS_Des_MAP', H.Text,0)<>0, H.Text,

    if(ApplyMap('WBS_Des_MAP',[Project Code],0)<>0,[Project Code],

    'C1000'

    ))))   

      as WBS_Final_Code,

      ApplyMap('AssignmentToWBSMap',Co.code,0) as WBS_Final_code,

    H.Text as Header_Text   

    Resident OutFlowData;

Regards,

Keerthi KS

[sujeetsingh]

WBS_Des_MAP:

Mapping

LOAD ID as WBS_Element,

    if(WildMatch([FINAL WBS],'*-'),Replace([FINAL WBS],'-',''),[FINAL WBS]) as WBS_Element_Des

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is DETAILS);

LOADING THE DATA FROM THE SHEET

WBS_Des_MAP2:

Mapping

LOAD

    [FINAL WBS],

    F3

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is [GROUP DESCR]);

LOADING NEXT TABLE DATA

WBS_Group_Des:

LOAD

    [FINAL WBS] as WBS_Code,

    F3,

    Descr

FROM

[..\10_data_sources\Excel\WBS MASTER-FINAL-CASHFLOW.xlsx]

(ooxml, embedded labels, table is [GROUP DESCR]);

AGAIN LOADING THE DATA FROM SAME SHEET

AssignmentToWBSMap:

Mapping

LOAD Co.code,

    if(len(trim([WBS Element]))>0,[WBS Element],

    if(Len(Assignment)>=6,Assignment,H.Text)) as WBS_Code_Final

FROM

[..\10_data_sources\Excel\cashflow-*.csv]

(txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes, no

eof);

CREATING MAPPING TABLE

LOAD

    'Current' as Cashflow_Period_Type,

    if(ApplyMap('WBS_Des_MAP', [WBS Element],0)<>0, [WBS Element],

    if(ApplyMap('WBS_Des_MAP',Assignment,0)<>0,Assignment,

    if(ApplyMap('WBS_Des_MAP', H.Text,0)<>0, H.Text,

    if(ApplyMap('WBS_Des_MAP',[Project Code],0)<>0,[Project Code],

    'C1000'

    ))))

      as WBS_Final_Code,

      ApplyMap('AssignmentToWBSMap',Co.code,0) as WBS_Final_code,

    H.Text as Header_Text

    Resident OutFlowData;

APPLYING MAPPING VALUE BASED ON THE Co.code

[sunny_talwar]

Hey keerthiks

Which part of the code is need more explanation? It seems pretty straight forward with multiple Mapping Loads . Do you need to know how Mapping Load or is there something else you want to understand.

Best,

Sunny

[#4]

Yea about mapping loads. Below code in detail

if(ApplyMap('WBS_Des_MAP', [WBS Element],0)<>0, [WBS Element],

    if(ApplyMap('WBS_Des_MAP',Assignment,0)<>0,Assignment,

    if(ApplyMap('WBS_Des_MAP', H.Text,0)<>0, H.Text,

    if(ApplyMap('WBS_Des_MAP',[Project Code],0)<>0,[Project Code],

    'C1000'

    ))))

      as WBS_Final_Code,

      ApplyMap('AssignmentToWBSMap',Co.code,0) as WBS_Final_code,

[sunny_talwar · Accepted solution]

if(ApplyMap('WBS_Des_MAP', [WBS Element],0)<>0, [WBS Element],

//If mapping is available for WBS Element, then use WBS Element from the current table (not from the mapped table)

if(ApplyMap('WBS_Des_MAP',Assignment,0)<>0,Assignment,

//If first if statement is not true (no mapping available for WBS Element), then check if mapping is available Assignment. If it does then use Assignment from the current table (not from the mapped table)

if(ApplyMap('WBS_Des_MAP', H.Text,0)<>0, H.Text,

//If the above if statement is not true (no mapping available for Assignment), then check if mapping is available H.Text. If it does then use H.Text from the current table (not from the mapped table)

if(ApplyMap('WBS_Des_MAP',[Project Code],0)<>0,[Project Code],

//If the above if statement is not true (no mapping available for H.Text), then check if mapping is available Project Code. If it does then use Project Code from the current table (not from the mapped table)

'C1000'

//If all four mappings are not available then use the Text 'C1000'

)))) as WBS_Final_Code,

ApplyMap('AssignmentToWBSMap',Co.code,0) as WBS_Final_code,

// This is regular mapping of Co.code where if its available in AssignmentToWBSMap, then show the mapping field, else show 0 for WBS_Final_code

HTH

Best,

Sunny

[#6]

why is it <>0 ?? It will take from current table not mapped table ?

[sunny_talwar]

If you want it to be taken from mapped table then you need this:

If(ApplyMap('WBS_Des_MAP', [WBS Element], 0) <> 0, ApplyMap('WBS_Des_MAP', [WBS Element], 0),

If(ApplyMap('WBS_Des_MAP',Assignment, 0) <> 0, ApplyMap('WBS_Des_MAP',Assignment, 0),

If(ApplyMap('WBS_Des_MAP', H.Text, 0) <> 0, ApplyMap('WBS_Des_MAP', H.Text, 0),

If(ApplyMap('WBS_Des_MAP',[Project Code], 0) <> 0, ApplyMap('WBS_Des_MAP',[Project Code], 0),

'C1000'

)))) as WBS_Final_Code,

[#8]

both the tables to be merged then what would be the code ?

[sunny_talwar]

Not sure what you mean?? Merge which tables??

[#10]

Both mapping table and WBS Element in the first condition.