Left Outer Join to same Resident Table

[QIT]

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.

[avinashelite]

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
]
;

[Kushal_Chawda]

@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;

[Kushal_Chawda]

@QIT  mark the appropriate answer as correct

[QIT]

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.

[Kushal_Chawda · Accepted solution]

@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;

[QIT]

Great! Thank you very much!