Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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
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;
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;
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