Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to concatenate 2 months' files (GL Transaction Extract LM.xlsm and GL Transaction Extract CM.xlsm)
But before this step is done. I need to map data of these 2 files with another 2 files , respectively.
1) GL Transaction Extract LM.xlsm Left Join Journal Listing LM.xlsx
2) Concatenate
3) GL Transaction Extract CM.xlsm Left Join Journal Listing CM.xlsx
After I run the data, the first pair in 1 above returns the correct mapping data
But the 2nd pair in 3) above did not return any mapping data
I have tried to create a separate script to test the 2nd pair without concatenation with last month.
It returns the correct mapping
The problem seems to happen when I try to concatenate the 2 together
May I seek some expert advice to solve the problem?
Below is my code:
[LM]:
LOAD
'LM' as period,
'LM'&SJE_LINK as Jnl#,
LEGAL_ENTITY,
SERVICE,
COST_CENTRE,
NOMINAL,
AFFILIATE,
GL_DATE,
POSTED_DATE,
ACCOUNTING_DATE,
TXN_AMT,
TXN_CURRENCY,
FUNCTIONAL_AMOUNT,
FUNCTIONAL_CURRENCY,
SJE_LINK,
LINE_TYPE_LOOKUP_CODE,
FROM [lib://Fusion/GL Transaction Extract LM.xlsm]
(ooxml, embedded labels, table is HBAP);
left Join ([LM])
LOAD
'LM'&"F27" as Jnl#,
Journal,
"Journal Batch",
F27,
"Reporting Sequence",
FROM [lib://Fusion/Journal listing LM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
Concatenate
LOAD
'CM' as period,
'CM'&SJE_LINK as Jnl#,
LEGAL_ENTITY,
SERVICE,
COST_CENTRE,
NOMINAL,
AFFILIATE,
GL_DATE,
POSTED_DATE,
ACCOUNTING_DATE,
TXN_AMT,
TXN_CURRENCY,
FUNCTIONAL_AMOUNT,
FUNCTIONAL_CURRENCY,
SJE_LINK,
LINE_TYPE_LOOKUP_CODE,
FROM [lib://Fusion/GL Transaction Extract CM.xlsm]
(ooxml, embedded labels, header is 4 lines, table is Data);
Left Join
LOAD
'CM'&"F27" as [Jnl#],
Journal,
"Journal Batch",
F27,
"Reporting Sequence",
FROM [lib://Fusion/Journal listing CM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
Hi,
here is the problem.
MAP_JNL2:
MAPPING LOAD
'CM'&"F27" as Jnl# // it should be CM inplace of LM //'LM'&"F27" as Jnl#,
Journal&'|'&"Journal Batch"&'|'&F27&'|'&"Reporting Sequence" as Value
FROM [lib://Fusion/Journal listing CM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
Hi,
you can use apply map with subfield to overcome with this problem.
MAP_JNL1:
MAPPING LOAD
'LM'&"F27" as Jnl#,
Journal&'|'&"Journal Batch"&'|'&F27&'|'&"Reporting Sequence" as Value
FROM [lib://Fusion/Journal listing LM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
MAP_JNL2:
MAPPING LOAD
'LM'&"F27" as Jnl#,
Journal&'|'&"Journal Batch"&'|'&F27&'|'&"Reporting Sequence" as Value
FROM [lib://Fusion/Journal listing CM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
[LM]:
LOAD
'LM' as period,
'LM'&SJE_LINK as Jnl#,
subfield(Applymap('MAP_JNL1','LM'&SJE_LINK,'#NA'),'|',1) as Journal,
subfield(Applymap('MAP_JNL1','LM'&SJE_LINK,'#NA'),'|',2) as "Journal Batch",
subfield(Applymap('MAP_JNL1','LM'&SJE_LINK,'#NA'),'|',3) as F27,
subfield(Applymap('MAP_JNL1','LM'&SJE_LINK,'#NA'),'|',4) as "Reporting Sequence" ,
LEGAL_ENTITY,
SERVICE,
COST_CENTRE,
NOMINAL,
AFFILIATE,
GL_DATE,
POSTED_DATE,
ACCOUNTING_DATE,
TXN_AMT,
TXN_CURRENCY,
FUNCTIONAL_AMOUNT,
FUNCTIONAL_CURRENCY,
SJE_LINK,
LINE_TYPE_LOOKUP_CODE,
FROM [lib://Fusion/GL Transaction Extract LM.xlsm]
(ooxml, embedded labels, table is HBAP);
Concatenate
LOAD
'CM' as period,
'CM'&SJE_LINK as Jnl#,
subfield(Applymap('MAP_JNL2','CM'&SJE_LINK,'#NA'),'|',1) as Journal,
subfield(Applymap('MAP_JNL2','CM'&SJE_LINK,'#NA'),'|',2) as "Journal Batch",
subfield(Applymap('MAP_JNL2','CM'&SJE_LINK,'#NA'),'|',3) as F27,
subfield(Applymap('MAP_JNL2','CM'&SJE_LINK,'#NA'),'|',4) as "Reporting
LEGAL_ENTITY,
SERVICE,
COST_CENTRE,
NOMINAL,
AFFILIATE,
GL_DATE,
POSTED_DATE,
ACCOUNTING_DATE,
TXN_AMT,
TXN_CURRENCY,
FUNCTIONAL_AMOUNT,
FUNCTIONAL_CURRENCY,
SJE_LINK,
LINE_TYPE_LOOKUP_CODE,
FROM [lib://Fusion/GL Transaction Extract CM.xlsm]
(ooxml, embedded labels, header is 4 lines, table is Data);
or you can first perform the left join with two sets of tables and then concrete the table 1 and table2
I hope this will help
Thanks,
Bhaskar
Thank you very much for your advice. It is very helpful
I have tried the applymap suggestion. I think it will resolve my problem. But when I run the script, I have error that Jnl# cannot be found.
So I am not sure where is the problem. Hope you would not mind to advice again. I very much appreciate your help.
Hi,
here is the problem.
MAP_JNL2:
MAPPING LOAD
'CM'&"F27" as Jnl# // it should be CM inplace of LM //'LM'&"F27" as Jnl#,
Journal&'|'&"Journal Batch"&'|'&F27&'|'&"Reporting Sequence" as Value
FROM [lib://Fusion/Journal listing CM.xlsx]
(ooxml, embedded labels, table is [Export to Excel]);
Thanks a lot for your guidance. I appreciate it a lot.