topic Re: Looking for the first, second... purchase price in App Development

Looking for the first, second... purchase price

Ernest97 — Thu, 21 Sep 2023 09:51:15 GMT

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.

Re: Looking for the first, second... purchase price

sbaro_bd — Fri, 22 Sep 2023 14:56:10 GMT

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.

Re: Looking for the first, second... purchase price

JGMDataAnalysis — Fri, 22 Sep 2023 18:09:11 GMT

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 =;

Re: Looking for the first, second... purchase price

Ernest97 — Mon, 25 Sep 2023 09:58:23 GMT

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.