Linking the latest product price to a list of product sales

[Francis_F]

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

[marcus_sommer · Accepted solution]

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_F]

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

[Brett_Bleess · Accepted solution]

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