Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

LEFT JOIN with a group by and calculated field

Dear all,

I'm unable to do a left join between two tables (Oracle table and excel file) where I need to group the data and have a calculet field between two field where one is on one table and the other is on the other one.

In particular my SQL statement is:

SELECT TESO_CASH_LEDGER.ACC_CODE, TESO_CASH_LEDGER.BANK_CODE, TESO_CASH_LEDGER.CMP_CODE, TESO_CASH_LEDGER.CUR_CODE, Sum([TESO_CASH_LEDGER]![AMOUNT]*[CAUSALISAGE]![Segno]) AS Total

FROM TESO_CASH_LEDGER LEFT JOIN CAUSALISAGE ON TESO_CASH_LEDGER.FLOW_CODE = CAUSALISAGE.Codice

GROUP BY TESO_CASH_LEDGER.ACC_CODE, TESO_CASH_LEDGER.BANK_CODE,

ORDER BY TESO_CASH_LEDGER.ACC_CODE, TESO_CASH_LEDGER.BANK_CODE, TESO_CASH_LEDGER.CMP_CODE;

now I understand that in qlik view in order to have the same I should do something like this:

CASH_LEDGER_master:

LOAD

     ACC_CODE,

     FLOW_CODE as LEGA,

     BANK_CODE,

     CMP_CODE,

     BUDGET_CODE,

     CUR_CODE,

     AMOUNT as importo

    FROM

 

(qvd);

left join (CASH_LEDGER_master)

CAUSALI:

LOAD Codice,

     [Tipo causale],

     Descrizione,

     Segno,

     Saldo,

     Rotazione,

     [Codice standard],

     Decrementazione,

     Commissione

FROM

(ooxml, embedded labels, table is Foglio1)

where CASH_LEDGER_master.LEGA = Codice;

final:

LOAD

  ACC_CODE,

     BANK_CODE,

     CMP_CODE,

     BUDGET_CODE,

     CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;

But this and all other try doesn't work. Could someone help me to understand the correct way in qlik view?

Thank you

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

This code looks correct to me. You can use this as is. Let us know if you still have issues after the above rename fix

final:

LOAD

  ACC_CODE,

    BANK_CODE,

    CMP_CODE,

    BUDGET_CODE,

    CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;


Edit

You have to include CMP_CODE,BUDGET_CODE,CUR_CODE in group by.


final:

LOAD

  ACC_CODE,

    BANK_CODE,

    CMP_CODE,

    BUDGET_CODE,

    CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE, CMP_CODE,    BUDGET_CODE,    CUR_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;

View solution in original post

5 Replies
anbu1984
Master III
Master III

In Qlikview, you cannot mention join keys like in Sql. You have to rename fields to same name in the source tables, then join happens automatically on similar fields

LOAD

     ACC_CODE,

     FLOW_CODE as LEGA,

     BANK_CODE,

     CMP_CODE,

     BUDGET_CODE,

     CUR_CODE,

     AMOUNT as importo

    FROM

 

(qvd);

left join (CASH_LEDGER_master)

CAUSALI:

LOAD Codice As LEGA,

     [Tipo causale],

     Descrizione,

     Segno,

     Saldo,

     Rotazione,

     [Codice standard],

     Decrementazione,

     Commissione

FROM

(ooxml, embedded labels, table is Foglio1)

//where CASH_LEDGER_master.LEGA = Codice;

//You cannot use field from another table in where clause like this. Instead rename Codice to LEGA

Not applicable
Author

Thank you anbu cheliyan but what about the need to createe a calculate field WSum([importo]*[Segno]) as Total" and the need to use the group by?

Thank you

anbu1984
Master III
Master III

This code looks correct to me. You can use this as is. Let us know if you still have issues after the above rename fix

final:

LOAD

  ACC_CODE,

    BANK_CODE,

    CMP_CODE,

    BUDGET_CODE,

    CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;


Edit

You have to include CMP_CODE,BUDGET_CODE,CUR_CODE in group by.


final:

LOAD

  ACC_CODE,

    BANK_CODE,

    CMP_CODE,

    BUDGET_CODE,

    CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE, CMP_CODE,    BUDGET_CODE,    CUR_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;

Not applicable
Author

Great!!!! I spent two days to understand this. Now for aal that can be interested here is the final code:

CASH_LEDGER_master:

LOAD

     ACC_CODE,

     FLOW_CODE as LEGA,

     BANK_CODE,

     CMP_CODE,

     BUDGET_CODE,

     CUR_CODE,

     AMOUNT as importo

    FROM

 

(qvd);

left join (CASH_LEDGER_master)

LOAD Codice As LEGA,

     Segno

FROM

(ooxml, embedded labels, table is Foglio1);

final:

LOAD

  ACC_CODE,

    BANK_CODE,

    CMP_CODE,

    CUR_CODE,

  Sum([importo]*[Segno]) as Total

resident CASH_LEDGER_master

group by ACC_CODE, BANK_CODE, CMP_CODE, CUR_CODE

ORDER BY ACC_CODE, BANK_CODE, CMP_CODE;

Purple
Contributor
Contributor

dont forget adding NoConcatenate at the start cuz qlik may do "union all" if u not say dont do this.

and drop the table (if you dont need it more) for best performance