Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an Acquisition table and a Stock one like the ones below.
I want to obtain a field that contains the most recent Invoice numbers for every item, until the stock value is obtain or is overfulfilled, and another field that sums the quantity of that invoices.
Acquisitions | |||
InvNo | InvDate | ItemNo | QtyAcq |
I1 | 03.03.2024 | A1 | 1 |
I2 | 05.10.2024 | A1 | 2 |
I3 | 06.11.2024 | A1 | 2 |
I4 | 02.04.2024 | A2 | 2 |
I5 | 06.05.2024 | A2 | 1 |
I6 | 08.09.2024 | A2 | 5 |
Stock | |
ItemNo | QtyStock |
A1 | 3 |
A2 | 5 |
For the example above, I want to obtain InvAcq and Sum(QtyAcq).
ItemNo | Sum(QtyAcq) | InvAcq |
A1 | 4 | I2, I3 |
A2 | 5 | I6 |
Please help me to solve the problem.
Thank you,
Teodora
@simonagheo I would use below script
Data:
Load * Inline [
InvNo InvDate ItemNo QtyAcq
I1 03.03.2024 A1 1
I2 05.10.2024 A1 2
I3 06.11.2024 A1 2
I4 02.04.2024 A2 2
I5 06.05.2024 A2 1
I6 08.09.2024 A2 5
](delimiter is '\t');
Left Join(Data)
Load * Inline [
ItemNo QtyStock
A1 3
A2 5
](delimiter is '\t');
T1:
NoConcatenate
Load *,
if(ItemNo=Previous(ItemNo),rangesum(Peek('AccumQtyAcq'),QtyAcq) ,QtyAcq) as AccumQtyAcq
Resident Data
Order by ItemNo,InvDate;
Drop Table Data;
Final:
NoConcatenate
Load *,
if(AccumQtyAcq>=QtyStock,1,0) as Flag
Resident T1;
drop table T1;
Create table with Dimension ItemNo and below two measures
=Concat({<Flag={1}>}InvNo,',') // Invoice
=sum({<Flag={1}>}QtyAcq) //Qty Acq
@simonagheo I would use below script
Data:
Load * Inline [
InvNo InvDate ItemNo QtyAcq
I1 03.03.2024 A1 1
I2 05.10.2024 A1 2
I3 06.11.2024 A1 2
I4 02.04.2024 A2 2
I5 06.05.2024 A2 1
I6 08.09.2024 A2 5
](delimiter is '\t');
Left Join(Data)
Load * Inline [
ItemNo QtyStock
A1 3
A2 5
](delimiter is '\t');
T1:
NoConcatenate
Load *,
if(ItemNo=Previous(ItemNo),rangesum(Peek('AccumQtyAcq'),QtyAcq) ,QtyAcq) as AccumQtyAcq
Resident Data
Order by ItemNo,InvDate;
Drop Table Data;
Final:
NoConcatenate
Load *,
if(AccumQtyAcq>=QtyStock,1,0) as Flag
Resident T1;
drop table T1;
Create table with Dimension ItemNo and below two measures
=Concat({<Flag={1}>}InvNo,',') // Invoice
=sum({<Flag={1}>}QtyAcq) //Qty Acq