Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

How to resolve Price Movement?

Hi Everyone,

I have this concern regarding price movement. I have two available tables: one is a sales data, the other one is a price history. I don't know yet what should I need to do to resolve the scenario. This is to know if the certain SKU is a REGULAR or REDUCED during the Sales Date.

SALES TABLE:

Sales.JPG

PRICE MOVEMENT TABLE:

PM.JPG

** Take note that in DATE TO the value has 999999 meaning that it is the current price of the SKU, or you can set it as Today().

Find below the desired output:

SalesTag.JPG

** Since this is QlikSense (to be displayed in). Would it be possible to done the solution in the script? Not in the formula? I was thinking to comeup a Price Movement masterlist where it is breakdown daily.

Thanks ahead for the help!

Regards,

Bill

10 Replies
Partner
Partner

Re: How to resolve Price Movement?

Hi Bill,

First you need to have an interval match dates for DateFrom and DateTo , then link your IntervalDate and SKU from Price Movement Table with Sales Date and SKU from Sales Table.

Partner
Partner

Re: How to resolve Price Movement?

Use Interval Match to find out on Particular Sales Date SKU was Regular or Reduced. and According to Key of SKU and Tag Left join the Price.

I hope it is helpful.

Partner
Partner

Re: How to resolve Price Movement?

I am not really good in interval match. Can you provide sample?

Highlighted
deepaktibhe
Valued Contributor

Re: How to resolve Price Movement?

Use Below script,

Table1:

LOAD SalesDate,

     SKU,

     QTY

FROM

C:\Users\DEEPAK\Desktop\Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD SKU,

     Datefrom,

     Dateto,

     TAG,

     Price

FROM

C:\Users\DEEPAK\Desktop\Book1.xlsx

(ooxml, embedded labels, table is Sheet2);

IntervalMatch:

IntervalMatch(SalesDate)

LEFT JOIN

LOAD Datefrom,

Dateto

Resident Table2;

Left Join(Table1)

LOAD * Resident

Table2;

DROP Table Table2;

Regards,

Deepak

Partner
Partner

Re: How to resolve Price Movement?

Have a look at IntervalMatch

Partner
Partner

Re: How to resolve Price Movement?

That's almost ok. But it shows some are not in good interval, see highlights...

result.JPG

Partner
Partner

Re: How to resolve Price Movement?

Please check with IntervalMatch table build after IntervalMatch() whether it worked correctly.

Partner
Partner

Re: How to resolve Price Movement?

Hi Bill,


Create a key of SKU and Sales Date from Sales table and a key of SKU and Interval Date from Price Movement Table  while left joining the tables.

Re: How to resolve Price Movement?

Row 1 and Row 3 have overlapping date range. Row 1 is from Feb 1 to Feb 29 and Row 3 is from Jan 1 till August 31st. So how would you choose which range is applicable here? I see you picked the Row 1 here for your sample, is there a reason you picked that?