How to resolve Price Movement?

[bill_mtc]

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:

PRICE MOVEMENT TABLE:

** 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:

** 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

[miskin_m]

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.

[shraddha_g]

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.

[bill_mtc]

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

[deepakqlikview_123]

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

[shraddha_g]

Have a look at IntervalMatch

[bill_mtc]

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

[shraddha_g]

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

[miskin_m]

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.

[sunny_talwar]

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?