Get Price from price list changes file on the basis of order date

[qluser01]

Hi!

I have a Table 1 with price list changes for a commodity:

Date of change	Commodity ID	New Price
2017 Jan 01	132	$10.5
2017 Jan 01	150	$20
2017 March 04	132	$12
2017 July 10	132	$15
2017 Aug 01	150	$25

(for about 1000 commodities and several years)

And I have a Table 2 with orders (but w/o prices), and I need to get correct price from Table 1 on the basis of order date so it should look like (marked red😞

Order Date	Commodity ID	Quantity	Unit price
2017 Jan 03	132	10	$10.5
2017 Jan 30	132	10	$10.5
2017 Mar 05	150	10	$20
2017 Aug 15	132	10	$15
2017 Aug 20	150	10	$25

Order date should be compared to price change dates, and the closest from the past should be taken (or the same date if order date equals date of change).

How it can be done using Qlik?

Thanks!

[Anonymous]

use IntervalMatch.

LET vCurrentDate = Today();

// ======================================================================

price:

LOAD

    "Date of change",

    "Commodity ID",

    "New Price"

FROM [lib://path_DB (rbcgrp_ostapchuk)/test\price_order.xlsx]

(ooxml, embedded labels, table is [Table 1]);

// ======================================================================

order:

LOAD

    "Order Date",

    "Commodity ID",

    Quantity

FROM [lib://path_DB (rbcgrp_ostapchuk)/test\price_order.xlsx]

(ooxml, embedded labels, table is [Table 2]);

// ======================================================================

Price_ID:

NoConcatenate

Load

"Commodity ID",

    "Date of change" as DateStart,

    date(if(Previous("Commodity ID")="Commodity ID",peek('DateStart')-1,'$(vCurrentDate)')) as DateEnd,

    "New Price"

Resident price

order By

"Commodity ID",

    "Date of change" desc;

Drop Tables price;

// ======================================================================

Inner Join IntervalMatch("Order Date","Commodity ID")

Load

DateStart,

    DateEnd,

    "Commodity ID"

Resident Price_ID;