Announcements
cancel
Showing results for
Did you mean:
Contributor

## Looking for the first, second... purchase price

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:

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:
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.

Labels (2)

• ### General Question

1 Solution

Accepted Solutions
Creator III

Completely resolved in script:

``````SET DateFormat = 'DD.MM.YYYY';

Purchases:
NOCONCATENATE
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
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
Article_No,
Date_sale
RESIDENT
Sales
;

INNER JOIN (LastestPurchasesByItemSalesTemp1)
RESIDENT Purchases
;

LastestPurchasesByItemSalesTemp2:
NOCONCATENATE
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)
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 =;``````
3 Replies
Creator

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:
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:
*
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:
IF([Date_sale] <= [Date_purchase],1,0) as Flag,
*

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.

Creator III

Completely resolved in script:

``````SET DateFormat = 'DD.MM.YYYY';

Purchases:
NOCONCATENATE
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
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
Article_No,
Date_sale
RESIDENT
Sales
;

INNER JOIN (LastestPurchasesByItemSalesTemp1)
RESIDENT Purchases
;

LastestPurchasesByItemSalesTemp2:
NOCONCATENATE
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)
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 =;``````
Contributor
Author

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.