- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 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
- Tags:
- qlikview_scripting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
hope it helps