topic Re: Interval Match in QlikView

Interval Match

vpanchuda — Sat, 16 Nov 2024 19:56:18 GMT

Can Some One help me with the below.

I have two tables as Below, In the price list table, the price shall be updated over time.

I want to link the transaction price on a particular date from the sales Table to the valid price on the date range in the price list.

I tried with several ways with interval match function but did not succeed to link the tables right.(My original data has more than 3 million rows)

PRICE LIST TABLE

Item No	Valid From	Valid To	Price
A1	20180801	20180905	10
A1	20180906	20190107	11
A1	20190108	20200101	12
B1	20170401	20190107	14
B1	20190108	20200208	16
C1	20170103	20190108	7
C1	20190109	20200101	9
D1	20180801	20181005	17
D1	20181006	20190607	18
D1	20190608	20200110	22

SALES TRANSACTION

Item NO	Transaction Date
A1	20180910
A1	20190210
B1	20180505
B1	20190420
C1	20190221
C1	20190508
D1	20181215
D1	20190818
D1	20191221

Thank you

Re: Interval Match

dplr-rn — Tue, 08 Oct 2019 18:42:53 GMT

check script below

PriceList: LOAD [Item No], [Valid From], [Valid To], Price FROM [SampleExcel.xlsx] (ooxml, embedded labels, table is Sheet1); Item: LOAD [Item NO] as [Item No], [Transaction Date] FROM SampleExcel.xlsx (ooxml, embedded labels, table is Sheet2); inner join IntervalMatch ([Transaction Date],[Item No]) Load distinct [Valid From], [Valid To],[Item No] resident PriceList ; //removing synthetic key (optional) left join(Item) load * resident PriceList;

Output

Item No	Transaction Date	Price
A1	20180910	11
A1	20190210	12
B1	20180505	14
B1	20190420	16
C1	20190221	9
C1	20190508	9
D1	20181215	18
D1	20190818	22
D1	20191221	22

Re: Interval Match

vpanchuda — Tue, 08 Oct 2019 19:58:43 GMT

Thank you Dilip , i made mistakes as below.

1- I just mentioned join instead of inner join.

2- I did not add the item no in Load Distinct.

you saved my day.

Re: Interval Match

dplr-rn — Wed, 09 Oct 2019 13:23:15 GMT

Not able to reply to you message for some reason. see below

----------------------------------

Just to make sure i understand it. So there is not valid to date in the excel but there is a logic to determine it. i.e. day before next valid from date.

If that is correct...

You might need to create the valid to date in the script while loading the table. check previous function.

So basically load the table in correct order i.e. product and validfrom date desc (latest valid from comes first) . Use previous function to populate the new field

roughly like below

if(previous([Item No])=[Item No],previous(validfrom) -1, today()) as {Valid To)

so for example new column would be something like below

Item No	Valid From	New Valid to column
A1	20190108	todays date
A1	20180906	20190107
A1	20180801	20180905
B1	20190108	todays date
B1	20170401	20190107

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Previous.htm

hope it helps