Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have 2 tables:
T1:
Order Timestamp | Sales | Order ID | MKT ID |
3/1/2018 0:00 | 100 | 2207874236 | 21598828 |
3/1/2018 1:00 | 50 | 2207874242 | 35660586 |
3/1/2018 1:00 | 90 | 2207874304 | 21598828 |
3/1/2018 12:00 | 60 | 2207874292 | 21598828 |
3/1/2018 13:00 | 60 | 2207874152 | 21598828 |
3/1/2018 13:00 | 70 | 2207874184 | 35660586 |
3/1/2018 18:00 | 30 | 2207874214 | 21598828 |
3/1/2018 22:00 | 300 | 2207874216 | 35660586 |
3/1/2018 22:00 | 170 | 2207874470 | 35660586 |
3/1/2018 22:00 | 40 | 2207874956 | 35660586 |
and T2 (without the last column, which i want to calculate)
Price Start Date | Price End Date | MKT ID | Sales |
3/1/2018 0:00 | 3/1/2018 11:00 | 21598828 | 190 |
3/1/2018 11:00 | 3/1/2018 18:00 | 21598828 | 120 |
3/1/2018 18:00 | 3/1/2018 23:59 | 21598828 | 30 |
3/1/2018 0:00 | 3/1/2018 9:00 | 35660586 | 50 |
3/1/2018 9:00 | 3/1/2018 23:59 | 35660586 | 580 |
How do i calculate in the script the last column from T2?
Regards,
Andrei
Hi,
What you're looking for is the interval match function, once applied, you will have the ability to take the price from T2 according to MKTID and the orderdate timestamp.
Please refer to Henric's blog about this subject to help you with this subject.
https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch
Regards,
Eliran.
Hi,
You can use intervalmatch function like :
t1:
load
Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],
Sales,
[Order ID],
[MKT ID]
Inline [
Order Timestamp, Sales, Order ID, MKT ID
3/1/2018 0:00, 100, 2207874236, 21598828
3/1/2018 1:00, 50, 2207874242, 35660586
3/1/2018 1:00, 90, 2207874304, 21598828
3/1/2018 12:00, 60, 2207874292, 21598828
3/1/2018 13:00, 60, 2207874152, 21598828
3/1/2018 13:00, 70, 2207874184, 35660586
3/1/2018 18:00, 30, 2207874214, 21598828
3/1/2018 22:00, 300, 2207874216, 35660586
3/1/2018 22:00, 170, 2207874470, 35660586
3/1/2018 22:00, 40, 2207874956, 35660586
];
t2:
Load
Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],
Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date],
[MKT ID]
Inline [
Price Start Date, Price End Date, MKT ID
3/1/2018 0:00, 3/1/2018 11:00, 21598828
3/1/2018 11:00, 3/1/2018 18:00, 21598828
3/1/2018 18:00, 3/1/2018 23:59, 21598828
3/1/2018 0:00, 3/1/2018 9:00, 35660586
3/1/2018 9:00, 3/1/2018 23:59, 35660586
];
Left Join(t2)
IntervalMatch([Order Timestamp])
Load
[Price Start Date],
[Price End Date]
Resident t2
;
Left Join(t2)
Load
[Order Timestamp],
Sales,
[MKT ID]
Resident t1
;
Drop Table t1;
tf:
Load
[Price Start Date],
[Price End Date],
[MKT ID],
Sum(Sales) as Sales
Resident t2
Group By
[Price Start Date],
[Price End Date],
[MKT ID]
;
Drop Table t2;
Try this
T1:LOAD
Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],Sales,
[Order ID],
[MKT ID] INLINE [
Order Timestamp, Sales, Order ID, MKT ID
3/1/2018 0:00, 100, 2207874236, 21598828
3/1/2018 1:00, 50, 2207874242, 35660586
3/1/2018 1:00, 90, 2207874304, 21598828
3/1/2018 12:00, 60, 2207874292, 21598828
3/1/2018 13:00, 60, 2207874152, 21598828
3/1/2018 13:00, 70, 2207874184, 35660586
3/1/2018 18:00, 30, 2207874214, 21598828
3/1/2018 22:00, 300, 2207874216, 35660586
3/1/2018 22:00, 170, 2207874470, 35660586
3/1/2018 22:00, 40, 2207874956, 35660586
];
T2:
LOAD Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],
Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date], [MKT ID] INLINE [
Price Start Date, Price End Date, MKT ID
3/1/2018 0:00, 3/1/2018 11:00, 21598828
3/1/2018 11:00, 3/1/2018 18:00, 21598828
3/1/2018 18:00, 3/1/2018 23:59, 21598828
3/1/2018 0:00, 3/1/2018 9:00, 35660586
3/1/2018 9:00, 3/1/2018 23:59, 35660586
];
left join(T2)
IntervalMatch([Order Timestamp])
load [Price Start Date], [Price End Date]
Resident T2;
left join(T2)
load
Sales, [Order Timestamp],[MKT ID]
Resident T1;
drop table T1;
Or you can use Key to remove synthetic key rather than join,
T1:LOAD
Timestamp#([Order Timestamp], 'MM/DD/YYYY hh:mm') as [Order Timestamp],Sales,
[Order ID],
[MKT ID] INLINE [
Order Timestamp, Sales, Order ID, MKT ID
3/1/2018 0:00, 100, 2207874236, 21598828
3/1/2018 1:00, 50, 2207874242, 35660586
3/1/2018 1:00, 90, 2207874304, 21598828
3/1/2018 12:00, 60, 2207874292, 21598828
3/1/2018 13:00, 60, 2207874152, 21598828
3/1/2018 13:00, 70, 2207874184, 35660586
3/1/2018 18:00, 30, 2207874214, 21598828
3/1/2018 22:00, 300, 2207874216, 35660586
3/1/2018 22:00, 170, 2207874470, 35660586
3/1/2018 22:00, 40, 2207874956, 35660586
];
T2:
LOAD Timestamp#([Price Start Date], 'MM/DD/YYYY hh:mm') as [Price Start Date],
Timestamp#([Price End Date], 'MM/DD/YYYY hh:mm') as [Price End Date], [MKT ID] INLINE [
Price Start Date, Price End Date, MKT ID
3/1/2018 0:00, 3/1/2018 11:00, 21598828
3/1/2018 11:00, 3/1/2018 18:00, 21598828
3/1/2018 18:00, 3/1/2018 23:59, 21598828
3/1/2018 0:00, 3/1/2018 9:00, 35660586
3/1/2018 9:00, 3/1/2018 23:59, 35660586
];
left join(T2)
IntervalMatch([Order Timestamp])
load [Price Start Date], [Price End Date]
Resident T2;
T3:
load [Price Start Date], [Price End Date], [MKT ID],[Order Timestamp],[Order Timestamp]&'|'&[MKT ID] as Key
Resident T2;
drop table T2;
T4:
load
Sales, [Order ID],[Order Timestamp]&'|'&[MKT ID] as Key
Resident T1;
drop table T1;