topic Re: Code Explanation in QlikView

Code Explanation

Fri, 07 Aug 2015 04:23:16 GMT

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],

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

Re: Code Explanation

sujeetsingh — Fri, 07 Aug 2015 04:35:50 GMT

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],

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

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 04:36:31 GMT

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

Re: Code Explanation

Fri, 07 Aug 2015 06:35:28 GMT

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,

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 06:45:49 GMT

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

Re: Code Explanation

Fri, 07 Aug 2015 06:50:50 GMT

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

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 06:53:58 GMT

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,

Re: Code Explanation

Fri, 07 Aug 2015 06:57:44 GMT

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

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 06:59:11 GMT

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

Re: Code Explanation

Fri, 07 Aug 2015 07:03:22 GMT

Both mapping table and WBS Element in the first condition.

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 07:06:22 GMT

Are you saying that you want to use Join instead of Mapping/ApplyMap method to achieve the same result as your original code???

Re: Code Explanation

Fri, 07 Aug 2015 07:07:32 GMT

Yes to join both tables

Re: Code Explanation

sunny_talwar — Fri, 07 Aug 2015 07:45:20 GMT

Need to see your script for OutFlowData to give better direction.

Re: Code Explanation

Fri, 07 Aug 2015 08:24:48 GMT

OutFlowData:

LOAD Co.code,

V.Code,

V.Name,

[Doc. Type],

[Payment Doc No],

[Line Item No],

[Payment Date],

[Spl G/LAcc],

[Paid amt],

[TDS Amt],

[Paid Amt+TDS],

[Line Item Amt],

[Inv Doc No],

Year,

[Bill No],

[Inv Date],

[Source Doc.Type],

[Inv Posting Date],

[Inv Amt],

Curr.,

[PO No],

[PO Item No],

[PO Date],

[PO Value],

[Service Activity],

[Activity Short Text],

Mat.Code,

Mat.Desc,

[WBS Element],

Assignment,

[Network Activity],

[Internal Order],

[Project Code],

[Project Name],

[Payment Method],

[Payment Ref.No],

[Cheque Date],

H.Text,

[G/L Acc.],

[G/L Desc.],

[Payee Bank],

[Name of Bank],

Cl.Doc.No,

Cl.Date

FROM

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

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

eof);