topic Re: LEFT JOIN with a group by and calculated field in QlikView

LEFT JOIN with a group by and calculated field

Tue, 23 Dec 2014 13:35:58 GMT

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

Re: LEFT JOIN with a group by and calculated field

anbu1984 — Tue, 23 Dec 2014 13:39:17 GMT

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

Re: LEFT JOIN with a group by and calculated field

Tue, 23 Dec 2014 13:43:24 GMT

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

Re: LEFT JOIN with a group by and calculated field

anbu1984 — Tue, 23 Dec 2014 13:47:32 GMT

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;

Re: LEFT JOIN with a group by and calculated field

Tue, 23 Dec 2014 14:06:22 GMT

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;

Re: LEFT JOIN with a group by and calculated field

un4given — Mon, 12 Feb 2024 06:48:53 GMT

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