Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
This is my first post on the forum so I would like to thank you for your help.
I created a table to test to find a solution, here is my data:
Purchasing_table:
LOAD *
Inline [
Date_purchase, Article_No, Quantity_purchase, Price_purchase
03.08.2023, 334443, 320, 221.5
07.07.2023, 334443, 960, 664.5
16.05.2023, 334443, 220, 156.38
09.05.2023, 334443, 100, 71.08
15.03.2023, 334443, 320, 227.47
08.03.2023, 334443, 320, 227.47
23.11.2022, 334443, 1600, 1107.5
16.08.2023, 124455, 1000, 150
13.07.2023, 124455, 500, 50
16.06.2023, 124455, 1000, 150
13.05.2023, 124455, 500, 50
];
Sales_table:
LOAD *
Inline [
Date_sale, Article_No, Quantity_sale, Price_purchase_sale
19.06.2023, 334443, 600, 438.4
15.05.2023, 334443, 450, 190
16.06.2023, 124455, 200, 200
13.04.2023, 124455, 500, 100
];
I would like to assign the first 3 found values from the purchase table to the sales table, the article number must match ([Article_No] = [Article_No]).
I know that this can be done using FirstSortedValue(),but there is another condition the date of sale must be less than or equal to the purchase date ([Date_sale] <= [Date_purchase]).
I would like to create a table like the one below, that is, for each sale, I would like to assign the last purchase using the conditions "[Article_No] = [Article_No] and [Date_sale] <= [Date_purchase]".
I am in the process of looking for a solution to this problem, if you have any suggestions I would be very grateful, if I find a solution I will share it.
Completely resolved in script:
SET DateFormat = 'DD.MM.YYYY';
Purchases:
NOCONCATENATE
LOAD *
INLINE [
Date_purchase, Article_No, Quantity_purchase, Price_purchase
03.08.2023, 334443, 320, 221.5
07.07.2023, 334443, 960, 664.5
16.05.2023, 334443, 220, 156.38
09.05.2023, 334443, 100, 71.08
15.03.2023, 334443, 320, 227.47
08.03.2023, 334443, 320, 227.47
23.11.2022, 334443, 1600, 1107.5
16.08.2023, 124455, 1000, 150
13.07.2023, 124455, 500, 50
16.06.2023, 124455, 1000, 150
13.05.2023, 124455, 500, 50
];
Sales:
NOCONCATENATE
LOAD *
INLINE [
Date_sale, Article_No, Quantity_sale, Price_purchase_sale
19.06.2023, 334443, 600, 438.4
15.05.2023, 334443, 450, 190
16.06.2023, 124455, 200, 200
13.04.2023, 124455, 500, 100
];
LastestPurchasesByItemSalesTemp1:
NOCONCATENATE
LOAD DISTINCT
Article_No,
Date_sale
RESIDENT
Sales
;
INNER JOIN (LastestPurchasesByItemSalesTemp1)
LOAD *
RESIDENT Purchases
;
LastestPurchasesByItemSalesTemp2:
NOCONCATENATE
LOAD *,
If(Article_No <> Peek(Article_No) OR Date_sale <> Peek(Date_sale), 1,
Peek(Purchase_No) + 1
) AS Purchase_No
RESIDENT
LastestPurchasesByItemSalesTemp1
WHERE
Date_purchase <= Date_sale
ORDER BY
Article_No,
Date_sale,
Date_purchase DESC
;
DROP TABLE LastestPurchasesByItemSalesTemp1;
FOR i = 1 TO 3
LEFT JOIN (Sales)
LOAD
Article_No,
Date_sale,
Date_purchase AS Date_purchase$(i),
Quantity_purchase AS Quantity_purchase$(i),
Price_purchase AS Price_purchase$(i)
RESIDENT
LastestPurchasesByItemSalesTemp2
WHERE
Purchase_No = $(i)
;
NEXT
DROP TABLE LastestPurchasesByItemSalesTemp2;
LET i =;
LET DateFormat =;
Hi @Ernest97 ,
First of all, join your two tables and create an unique table. After that, create a new calculated dimension (a flag):
Purchasing_table:
LOAD *
Inline [
Date_purchase, Article_No, Quantity_purchase, Price_purchase
03.08.2023, 334443, 320, 221.5
07.07.2023, 334443, 960, 664.5
16.05.2023, 334443, 220, 156.38
09.05.2023, 334443, 100, 71.08
15.03.2023, 334443, 320, 227.47
08.03.2023, 334443, 320, 227.47
23.11.2022, 334443, 1600, 1107.5
16.08.2023, 124455, 1000, 150
13.07.2023, 124455, 500, 50
16.06.2023, 124455, 1000, 150
13.05.2023, 124455, 500, 50
];
Sales_table:
JOIN (Purchasing_table)
LOAD
*
Inline [
Date_sale, Article_No, Quantity_sale, Price_purchase_sale
19.06.2023, 334443, 600, 438.4
15.05.2023, 334443, 450, 190
16.06.2023, 124455, 200, 200
13.04.2023, 124455, 500, 100
];
NoConcatenate
MASTER_TAB:
LOAD
IF([Date_sale] <= [Date_purchase],1,0) as Flag,
*
RESIDENT Purchasing_table;
DROP TABLE Purchasing_table;
Normally, you'll be able to use this calculated like a flag in your Firstsortedvalue() expression (with a set analysis). Test it and comeback to us.
Regards.
Completely resolved in script:
SET DateFormat = 'DD.MM.YYYY';
Purchases:
NOCONCATENATE
LOAD *
INLINE [
Date_purchase, Article_No, Quantity_purchase, Price_purchase
03.08.2023, 334443, 320, 221.5
07.07.2023, 334443, 960, 664.5
16.05.2023, 334443, 220, 156.38
09.05.2023, 334443, 100, 71.08
15.03.2023, 334443, 320, 227.47
08.03.2023, 334443, 320, 227.47
23.11.2022, 334443, 1600, 1107.5
16.08.2023, 124455, 1000, 150
13.07.2023, 124455, 500, 50
16.06.2023, 124455, 1000, 150
13.05.2023, 124455, 500, 50
];
Sales:
NOCONCATENATE
LOAD *
INLINE [
Date_sale, Article_No, Quantity_sale, Price_purchase_sale
19.06.2023, 334443, 600, 438.4
15.05.2023, 334443, 450, 190
16.06.2023, 124455, 200, 200
13.04.2023, 124455, 500, 100
];
LastestPurchasesByItemSalesTemp1:
NOCONCATENATE
LOAD DISTINCT
Article_No,
Date_sale
RESIDENT
Sales
;
INNER JOIN (LastestPurchasesByItemSalesTemp1)
LOAD *
RESIDENT Purchases
;
LastestPurchasesByItemSalesTemp2:
NOCONCATENATE
LOAD *,
If(Article_No <> Peek(Article_No) OR Date_sale <> Peek(Date_sale), 1,
Peek(Purchase_No) + 1
) AS Purchase_No
RESIDENT
LastestPurchasesByItemSalesTemp1
WHERE
Date_purchase <= Date_sale
ORDER BY
Article_No,
Date_sale,
Date_purchase DESC
;
DROP TABLE LastestPurchasesByItemSalesTemp1;
FOR i = 1 TO 3
LEFT JOIN (Sales)
LOAD
Article_No,
Date_sale,
Date_purchase AS Date_purchase$(i),
Quantity_purchase AS Quantity_purchase$(i),
Price_purchase AS Price_purchase$(i)
RESIDENT
LastestPurchasesByItemSalesTemp2
WHERE
Purchase_No = $(i)
;
NEXT
DROP TABLE LastestPurchasesByItemSalesTemp2;
LET i =;
LET DateFormat =;
Thanks a lot 😀
I have run the script against a larger amount of data and the calculations have run successfully.
I've worked quite a bit with Excel before and it does things a bit differently for such searches.
Thanks again for your help 😁, you've directed me to the logic of how such information can be combined.