Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qluser01
New Contributor III

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

Hi!

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

Date of change
Commodity ID
New Price
2017 Jan 01132$10.5
2017 Jan 01150$20
2017 March 04132$12
2017 July 10132$15
2017 Aug 01150$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 0313210$10.5
2017 Jan 3013210$10.5
2017 Mar 0515010$20
2017 Aug 1513210$15
2017 Aug 2015010$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!

Tags (2)
1 Reply
ivostapchuk
New Contributor III

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

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;   

Community Browser