Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Code Explanation

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

1 Solution

Accepted Solutions
Highlighted

Re: Code Explanation

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

View solution in original post

13 Replies
Highlighted
sujeetsingh
Honored Contributor III

Re: Code Explanation

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

Highlighted

Re: Code Explanation

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

Highlighted
Not applicable

Re: Code Explanation

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,

Highlighted

Re: Code Explanation

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

View solution in original post

Highlighted
Not applicable

Re: Code Explanation

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

Highlighted

Re: Code Explanation

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,

Highlighted
Not applicable

Re: Code Explanation

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

Highlighted

Re: Code Explanation

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

Highlighted
Not applicable

Re: Code Explanation

Both mapping table and WBS Element in the first condition.