Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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,
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
why is it <>0 ?? It will take from current table not mapped table ?
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,
both the tables to be merged then what would be the code ?
Not sure what you mean?? Merge which tables??
Both mapping table and WBS Element in the first condition.