Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
qluser01
Creator
Creator

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!

1 Reply
Anonymous
Not applicable

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;