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

[43918084]

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

[bhaskar_sm]

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

[43918084]

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.

[bhaskar_sm · Accepted solution]

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

[43918084]

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