Match the price to unit - Qlik Community

FFBB1983 · ‎2019-09-04

Dear Community,

I have the sales report from the warehouses. I can find how many units of product got sold from there.

In order to get the revenue, I need to match the price to the unit sales for different provinces and different period of time. We have different price in different province and increased price once a while. This makes me find it is hard to match the price against the unit sales.

I have the source files look like this,

Price log,

PROVINCE	ADJ_PRICE	START_MTH	END_MTH
BC	465.6	201706	201903
BC	476.77	201904
AB	470.4	201704	201903
AB	479.81	201904
SK	470.4	201804	201903
SK	479.81	201904
ON	460.8	201704	201711
ON	465.6	201712	201805
ON	470.4	201806	201904
ON	479.81	201905
YT	465.6	201704	201903
NU	465.6	201704	201903

Unit Sales Report

Date	Customer Province	Transaction Type	Units Sold/Returned
4/4/2016	ON	Sales	1
4/15/2016	BC	Sales	1
4/17/2016	ON	Sales	1
4/28/2016	AB	Sales	1
5/5/2016	ON	Sales	5
5/6/2016	ON	Sales	1
5/8/2016	ON	Sales	1
5/24/2016	ON	Sales	1
5/24/2016	ON	Sales	1
5/31/2016	ON	Sales	1
6/2/2016	ON	Sales	2
6/2/2016	ON	Sales	2
6/10/2016	ON	Sales	2
6/10/2016	SK	Sales	1
6/20/2016	QC	Sales	1
6/21/2016	ON	Sales	1
6/23/2016	QC	Sales	1
7/1/2016	ON	Sales	2
7/3/2016	ON	Sales	4
7/4/2016	NL	Sales	2
7/9/2016	BC	Sales	2
7/10/2016	ON	Sales	1
7/13/2016	ON	Sales	1
7/24/2016	ON	Sales	1

Can anyone kindly take a look at this?

neelamsaroha157 · ‎2019-09-04

You can create date from your YYYYMM fields as

Date(MakeDate(Left(START_MTH,4), Right(START_MTH,2), 01), 'MM/DD/YYYY') as StartDate,
Date(If(Len(END_MTH)<1, Today(), MakeDate(Left(END_MTH,4), Right(END_MTH,2), 01)), 'MM/DD/YYYY') as EndDate

and then use interval match as

innerJoin(YourTable)
IntervalMatch(Date,PROVINCE)
LOAD StartDate, EndDate, PROVINCE Resident YourTable;

FFBB1983 · ‎2019-09-05

Thanks for your help. The IntervalMatch sounds about right. But I got two issues with those scripts,

1) Syntetic key got created,

2) I still cound not match the price against the unit sales in a given province and in a given time.

here are what I wrote,

LIB CONNECT TO 'Dropbox (#$$##@)';

PriceTable:
LOAD
PROVINCE,
ADJ_PRICE,
Date(MakeDate(Left(START_MTH,4), Right(START_MTH,2), 01), 'MM/DD/YYYY') as StartDate,
Date(If(Len(END_MTH)<1, Today(), MakeDate(Left(END_MTH,4), Right(END_MTH,2), 01)), 'MM/DD/YYYY') as EndDate

FROM [lib://Dropbox (#$$##@)/Qlik Case\Price increase log.xlsx]
(ooxml, embedded labels, table is Sheet1);

WMT:
LOAD
"Date",
"Customer Province",
"Transaction Type",
"Units Sold/Returned"

FROM [lib://Dropbox (#$$##@)/Qlik Case\Unit Sales Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join(WMT)
IntervalMatch(Date,PROVINCE)
LOAD StartDate, EndDate, PROVINCE
Resident PriceTable;

Can you please take look what else I still need to change?

neelamsaroha157 · ‎2019-09-06

Sample1:
LOAD * INLINE [
PROVINCE ADJ_PRICE START_MTH END_MTH
BC 465.6 201706 201903
BC 476.77 201904
AB 470.4 201704 201903
AB 479.81 201904
SK 470.4 201804 201903
SK 479.81 201904
ON 460.8 201704 201711
ON 465.6 201712 201805
ON 470.4 201806 201904
ON 479.81 201905
YT 465.6 201704 201903
NU 465.6 201704 201903
](delimiter is ' ');

Table1:
NoConcatenate
LOAD PROVINCE,
ADJ_PRICE,
START_MTH,
END_MTH,
Date(MakeDate(Left(START_MTH,4), Right(START_MTH,2), 01), 'MM/DD/YYYY') as StartDate,
Date(If(Len(END_MTH)<1, Today(), MakeDate(Left(END_MTH,4), Right(END_MTH,2), 01)), 'MM/DD/YYYY') as EndDate
Resident Sample1;
DROP Table Sample1;

Sample2:
LOAD * INLINE [
Date Customer Province Transaction Type Units Sold/Returned
4/4/2018 ON Sales 1
4/15/2018 BC Sales 1
4/15/2019 BC Sales 5
4/17/2018 ON Sales 1
1/1/2019 AB Sales 1
5/5/2018 ON Sales 5
5/6/2018 ON Sales 1
5/8/2017 ON Sales 1
5/24/2017 ON Sales 1
5/24/2017 ON Sales 1
5/31/2017 ON Sales 1
6/2/2017 ON Sales 2
6/2/2018 ON Sales 2
6/10/2018 ON Sales 2
6/10/2018 SK Sales 1
6/20/2018 QC Sales 1
6/21/2018 ON Sales 1
6/23/2018 QC Sales 1
7/1/2017 ON Sales 2
7/3/2017 ON Sales 4
7/4/2017 NL Sales 2
7/9/2017 BC Sales 2
7/10/2017 ON Sales 1
7/13/2017 ON Sales 1
7/24/2018 ON Sales 1
](delimiter is ' ');

Table2:
LOAD Date(Date, 'MM/DD/YYYY') as TxnDate,
[Customer Province] as PROVINCE,
[Transaction Type],
[Units Sold/Returned]
Resident Sample2;
DROP Table Sample2;

inner Join(Table1)
IntervalMatch(TxnDate,PROVINCE)
LOAD StartDate, EndDate, PROVINCE Resident Table1;

FFBB1983 · ‎2019-09-09

Thanks neelamsaroha157.

I am able to connect to price to the unit now. But your script created a synthetic key. Is that ok to leave it? If I want to have it removed, what is the best way?

Also, I got an error msg when I add "[Units_Sold/Returned] * ADJ_PRICE AS TotalSales". The goal of this exercise is to apply various prices and to get the total revenue in $.

Can you please advise how to change the script and make it happen? Attached is the QVF. Much appreciated!

neelamsaroha157 · ‎2019-09-09

Hello @FFBB1983 ,

You don't have both the fields in same table and hence cannot apply operations on them. You'll need to join the two table to get what you need. Try this out -

Sample1:
LOAD * INLINE [
PROVINCE ADJ_PRICE START_MTH END_MTH
BC 465.6 201706 201903
BC 476.77 201904
AB 470.4 201704 201903
AB 479.81 201904
SK 470.4 201804 201903
SK 479.81 201904
ON 460.8 201704 201711
ON 465.6 201712 201805
ON 470.4 201806 201904
ON 479.81 201905
YT 465.6 201704 201903
NU 465.6 201704 201903
](delimiter is ' ');

Table1:
NoConcatenate
LOAD PROVINCE,
ADJ_PRICE,
START_MTH,
END_MTH,
Date(MakeDate(Left(START_MTH,4), Right(START_MTH,2), 01), 'MM/DD/YYYY') as StartDate,
Date(If(Len(END_MTH)<1, Today(), MakeDate(Left(END_MTH,4), Right(END_MTH,2), 01)), 'MM/DD/YYYY') as EndDate
Resident Sample1;
DROP Table Sample1;

Sample2:
LOAD * INLINE [
Date Customer Province Transaction Type Units Sold/Returned
4/4/2018 ON Sales 1
4/15/2018 BC Sales 1
4/15/2019 BC Sales 5
4/17/2018 ON Sales 1
1/1/2019 AB Sales 1
5/5/2018 ON Sales 5
5/6/2018 ON Sales 1
5/8/2017 ON Sales 1
5/24/2017 ON Sales 1
5/24/2017 ON Sales 1
5/31/2017 ON Sales 1
6/2/2017 ON Sales 2
6/2/2018 ON Sales 2
6/10/2018 ON Sales 2
6/10/2018 SK Sales 1
6/20/2018 QC Sales 1
6/21/2018 ON Sales 1
6/23/2018 QC Sales 1
7/1/2017 ON Sales 2
7/3/2017 ON Sales 4
7/4/2017 NL Sales 2
7/9/2017 BC Sales 2
7/10/2017 ON Sales 1
7/13/2017 ON Sales 1
7/24/2018 ON Sales 1
](delimiter is ' ');

Table2:
outer Join(Table1)
LOAD Date(Date, 'MM/DD/YYYY') as TxnDate,
[Customer Province] as PROVINCE,
[Transaction Type],
[Units Sold/Returned]
Resident Sample2;
DROP Table Sample2;

inner Join(Table1)
IntervalMatch(TxnDate,PROVINCE)
LOAD StartDate, EndDate, PROVINCE Resident Table1;

NoConcatenate
LOAD *, [Units Sold/Returned]*ADJ_PRICE as Rev Resident Table1;
DROP Table Table1;