Iterating on data between two tables

[qlikcook]

Hi there!

I am preparing a data set which resides in two database tables associated by a common attribute ("CPN").

Table A (warehouse qty on hand):
[ProdID]
[QOH]

Table B (products needed):
[ProdID]
[Deadline]
[QTY] (always = 1)
Order By [Deadline] asc

I need to conduct a lookup - for each row - between Table A and Table B and for each row to subtract [QTY] from [QOH].
Table B is ordered by [Deadline] as I need the products with the closest deadline to be treated first.

Imagined outcome:
Option 1) A flag in Table B which indicates if QOH for a ProdID has reached zero. I will then be able to create a list of products we're able to produce with the current warehouse QOH stats.

Option 2) A column in Table B stating what the adjusted QOH is for the record's ProdID (okay if it's a negative number).

Hope someone is able to crack the case.

[Dalton_Ruer · Accepted solution]

Here is an example of how it can be done using Previous and Peek functions. 

Data:
Load * Inline [
ProdID, StartingQOH
A, 6
B, 4
];

Join
Load * Inline [
ProdID, Deadline, Qty
A, 1/09/2021, 2
A, 1/12/2021, 2
A, 3/21/2021, 1
A, 4/12/2021, 4
B, 1/06/2021, 1
B, 1/17/2021, 3
B, 2/16/2021, 2
];

Join (Data)
Load ProdID,
Deadline,
If(ProdID = Previous(ProdID), Peek(RemainingQOH)-Qty, StartingQOH-Qty) as RemainingQOH
Resident Data
order by ProdID, Deadline;

The code above yields these results.

[qlikcook]

Thank you very much @Dalton_Ruer .

What an elegant solution.