Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vpanchuda
Contributor III
Contributor III

Interval Match

Hi 

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 NoValid FromValid ToPrice
A1201808012018090510
A1201809062019010711
A1201901082020010112
B1201704012019010714
B1201901082020020816
C120170103201901087
C120190109202001019
D1201808012018100517
D1201810062019060718
D1201906082020011022

 

SALES TRANSACTION

Item NOTransaction Date
A120180910
A120190210
B120180505
B120190420
C120190221
C120190508
D120181215
D120190818
D120191221

 

Thank you

Labels (1)
1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

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 NoTransaction DatePrice
A12018091011
A12019021012
B12018050514
B12019042016
C1201902219
C1201905089
D12018121518
D12019081822
D12019122122

 

View solution in original post

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

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 NoTransaction DatePrice
A12018091011
A12019021012
B12018050514
B12019042016
C1201902219
C1201905089
D12018121518
D12019081822
D12019122122

 

vpanchuda
Contributor III
Contributor III
Author

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.

dplr-rn
Partner - Master III
Partner - Master III

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 NoValid FromNew Valid to column
A120190108todays date
A12018090620190107
A12018080120180905
B120190108todays date
B12017040120190107

 

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

 

hope it helps