topic Re: [SOLVED] Left Outer Join to same Resident Table in QlikView

Left Outer Join to same Resident Table

QIT — Fri, 09 Jul 2021 13:52:24 GMT

Hello all.

I have a simple resident table (loaded from excel file) like:

yyyymm|code|balance 202101| A | 100 202102| A | 110 202103| A | 120 202104| A | 100 202105| A | 140 202106| A | 110 202107| A | 150 202101| B | 200 202102| B | 230 202103| B | 220 202104| B | 250 202105| B | 210 202106| B | 230 202107| B | 250 202101| C | 290 202102| C | 330 202103| C | 350 202104| C | 350 202105| C | 300 202106| C | 380 202107| C | 320

I would like to LEFT JOIN this table with itself in order to get, for each month (YYYYMM), the balance of the previous month for the same code.

I can do this in SQL (MS SQL Server) with both of these queries:

SELECT *,balance-prev_balance AS diff FROM ( SELECT yyyymm, code, balance, LAG(balance,1) OVER (PARTITION BY code ORDER BY yyyymm) prev_balance FROM UserhrNPF.test ) x;

SELECT *,balance-prev_balance AS diff FROM ( SELECT c.*, (SELECT TOP 1 p.balance FROM UserhrNPF.test p WHERE p.code = c.code AND p.yyyymm < c.yyyymm ORDER BY p.yyyymm DESC) prev_balance FROM UserhrNPF.test c ) x;

... but I am not able to do this with Qlik View.

The desired result should be like this:

yyyymm|code|balance|prev_balance|diff 202101| A | 100| | 202102| A | 110| 100| 10 202103| A | 120| 110| 10 202104| A | 100| 120| -20 202105| A | 140| 100| 40 202106| A | 110| 140| -30 202107| A | 150| 110| 40 202101| B | 200| | 202102| B | 230| 200| 30 202103| B | 220| 230| -10 202104| B | 250| 220| 30 202105| B | 210| 250| -40 202106| B | 230| 210| 20 202107| B | 250| 230| 20 202101| C | 290| | 202102| C | 330| 290| 40 202103| C | 350| 330| 20 202104| C | 350| 350| 0 202105| C | 300| 350| -50 202106| C | 380| 300| 80 202107| C | 320| 380| -60

Thanks for any help.

Re: Left Outer Joint to same Resident Table

avinashelite — Thu, 08 Jul 2021 11:03:15 GMT

Find the solution below

LOAD *,
balance-prev_balance as diff;
LOAD *,
Previous(balance) as prev_balance;
LOAD * Inline
[
yyyymm,code,balance
202101, A , 100
202102, A , 110
202103, A , 120
202104, A , 100
202105, A , 140
202106, A , 110
202107, A , 150
202101, B , 200
202102, B , 230
202103, B , 220
202104, B , 250
202105, B , 210
202106, B , 230
202107, B , 250
202101, C , 290
202102, C , 330
202103, C , 350
202104, C , 350
202105, C , 300
202106, C , 380
202107, C , 320
]
;

Re: Left Outer Joint to same Resident Table

Kushal_Chawda — Thu, 08 Jul 2021 13:24:44 GMT

@QIT try below

Data: LOAD yyyymm, code, balance FROM table; Final: LOAD *, previous(balance) as prev_bal, balance - previous(balance) as diff resident Data order by code,yyyymm; drop table Data;

Re: [SOLVED] Left Outer Join to same Resident Table

Kushal_Chawda — Thu, 08 Jul 2021 22:16:30 GMT

@QIT mark the appropriate answer as correct

Re: Left Outer Join to same Resident Table

QIT — Fri, 09 Jul 2021 14:19:25 GMT

Sorry but the problem seems a bit more complex.
My queries take the previous values only if the "code" is the same while in the proposed solutions the previous ones is taken even if the code is different (instead of null).

Thanks.

Re: Left Outer Join to same Resident Table

Kushal_Chawda — Fri, 09 Jul 2021 14:24:06 GMT

@QIT try below

Data: LOAD yyyymm, code, balance FROM table; Final: LOAD *, if(code=previous(code), previous(balance)) as prev_bal, if(code=previous(code),balance-previous(balance)) as diff resident Data order by code,yyyymm; drop table Data;

Re: Left Outer Join to same Resident Table

QIT — Fri, 09 Jul 2021 16:24:34 GMT

Great! Thank you very much!