Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikcook
Contributor III
Contributor III

Iterating on data between two tables

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.

Labels (1)
1 Solution

Accepted Solutions
Dalton_Ruer
Support
Support

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.

PreviousPeek.png

View solution in original post

2 Replies
Dalton_Ruer
Support
Support

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.

PreviousPeek.png

qlikcook
Contributor III
Contributor III
Author

Thank you very much @Dalton_Ruer .

What an elegant solution.