Looking for the first, second... purchase price

[Ernest97]

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.

[sbaro_bd]

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.

[JGMDataAnalysis · Accepted solution]

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

[Ernest97]

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.