LEFT JOIN with a group by and calculated field

[#1]

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

[anbu1984]

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

[#3]

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 · Accepted solution]

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;

[#5]

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]

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