Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Ernest97
Contributor
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:
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]".

Ernest97_1-1695284970404.png

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)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

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

View solution in original post

3 Replies
sbaro_bd
Creator
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:
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
Creator III
Creator III

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