Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have three tables of sales, products (with multiple historical product prices) and payors (with multiple sequential payors for each invoice Nr)and would like to link the three tables but show, for the table of sales, only the one distinct product price that preceded the sale and only the one distinct payor with the highest sequence number.
Below is the unedited script and I would be grateful for any help on how to edit it.
Many thanks
LOAD
Date_sale,
Client_Nr,
Product_Nr,
Invoice_Nr
FROM
[...\Data_Sales.xlsx]
(ooxml, embedded labels, table is Sales);
LOAD
Product_Nr,
Date_Price, (I need only the product price of the last date that preceded the sale, not all prices)
Price
FROM
[...\Data_Sales.xlsx]
(ooxml, embedded labels, table is Products);
LOAD
Invoice_Nr,
Sequence_Nr, (I need only the payor with the highest sequence number, unless it's "9")
Payor
FROM
[...\Data_Sales.xlsx]
(ooxml, embedded labels, table is Payor) ;
If I understand it right that you don't want the latest price (which is quite easy to get by self-(inner)joining the price-table and joining/mapping the result to the sales-table) else the right price to the sales-date then you will need to extend your price-table to a from/to price-date and then applying an intervalmatch-logic to match it to the sales-table.
Here an quite similar example: Ermitteln-eines-Datensatzes-welcher-das-maximale-aber-kleinere - is in German but google translate would be helpful - and if you searched for the used key-words you will find multiple postings to this topic.
- Marcus
Francis, here is another link for you from the Design Blog area on IntervalMatch, may help you further in addition to the Help as well. If Marcus had the right answer, consider using the Accept as Solution button on his post to give him credit as well.
https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547
Regards,
Brett
If I understand it right that you don't want the latest price (which is quite easy to get by self-(inner)joining the price-table and joining/mapping the result to the sales-table) else the right price to the sales-date then you will need to extend your price-table to a from/to price-date and then applying an intervalmatch-logic to match it to the sales-table.
Here an quite similar example: Ermitteln-eines-Datensatzes-welcher-das-maximale-aber-kleinere - is in German but google translate would be helpful - and if you searched for the used key-words you will find multiple postings to this topic.
- Marcus
Thank you Marcus,
it's indeed the product price that was valid in the interval prceding the sales date. I'm not familiar with the Intervalmatch logic but will look into this. As for your german exemple ... I'm lucky as I speak german too.
Mit besten Grüssen
Francis
Francis, here is another link for you from the Design Blog area on IntervalMatch, may help you further in addition to the Help as well. If Marcus had the right answer, consider using the Accept as Solution button on his post to give him credit as well.
https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547
Regards,
Brett