Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
43918084
Creator II
Creator II

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

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

Accepted Solutions
bhaskar_sm
Partner - Creator III
Partner - Creator III

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

View solution in original post

4 Replies
bhaskar_sm
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II
Author

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
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II
Author

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