topic Iterating on data between two tables in App Development

Iterating on data between two tables

qlikcook — Sat, 16 Nov 2024 00:15:31 GMT

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.

Re: Looping on data residing in two tables

Dalton_Ruer — Tue, 20 Apr 2021 19:16:51 GMT

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.

Re: Looping on data residing in two tables

qlikcook — Fri, 23 Apr 2021 07:31:57 GMT

Thank you very much @Dalton_Ruer .

What an elegant solution.