topic Re: File1 left join File A then concatenate File2 left join FileB. but the later left join fail in App Development

File1 left join File A then concatenate File2 left join FileB. but the later left join fail

43918084 — Sat, 30 May 2020 08:26:29 GMT

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]);

Re: File1 left join File A then concatenate File2 left join FileB. but the later left join fail

bhaskar_sm — Sat, 30 May 2020 10:49:45 GMT

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

Re: File1 left join File A then concatenate File2 left join FileB. but the later left join fail

43918084 — Sat, 30 May 2020 12:44:03 GMT

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.

Re: File1 left join File A then concatenate File2 left join FileB. but the later left join fail

bhaskar_sm — Sun, 31 May 2020 04:31:22 GMT

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]);

Re: File1 left join File A then concatenate File2 left join FileB. but the later left join fail

43918084 — Sun, 31 May 2020 08:02:48 GMT

Thanks a lot for your guidance. I appreciate it a lot.