Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Table 1
ProdcutId | Price | Date | Unit | Sale |
1 | 15 | 01/01/14 | 56 | 840 |
2 | 25 | 06/01/15 | 25 | 625 |
3 | 46 | 09/01/12 | 26 | 1196 |
4 | 48 | 01/01/08 | 78 | 3744 |
Table 2
ProductId | Price_Alt | Start Date | End Date |
1 | 14 | 09/01/13 | 04/01/14 |
1 | 15 | 01/01/14 | 01/01/15 |
1 | 10 | 01/01/09 | 01/01/10 |
1 | 16 | 12/01/13 | 06/01/14 |
2 | 25 | 03/01/15 | 12/01/15 |
2 | 22 | 06/01/15 | 12/01/15 |
2 | 23 | 09/01/15 | 12/01/15 |
2 | 20 | 01/01/09 | 01/01/10 |
3 | 46 | 09/01/12 | 12/01/12 |
4 | 48 | 01/01/08 | 01/01/09 |
4 | 46 | 03/01/08 | 6/1/2008 |
Output Table
ProductId | Price | Date | Unit | Sale | Price_Alt |
1 | 15 | 01/01/14 | 56 | 840 | 14 |
2 | 25 | 06/01/15 | 25 | 625 | 23 |
3 | 46 | 09/01/12 | 26 | 1196 | 46 |
4 | 48 | 01/01/08 | 78 | 3744 | 48 |
The Price_Alt value is the Min(Price_Alt) from Table 2 from the Date Period to which the Date field from Table1 belongs.
Not able to get the same using Interval Match
Try this:
Table1:
LOAD ProdcutId as ProductId,
Price,
Date,
Unit,
Sale
FROM
[https://community.qlik.com/thread/237306]
(html, codepage is 1252, embedded labels, table is @1);
Table2:
LOAD ProductId,
Price_Alt,
[Start Date],
[End Date]
FROM
[https://community.qlik.com/thread/237306]
(html, codepage is 1252, embedded labels, table is @2);
Left Join(Table1)
IntervalMatch(Date, ProductId)
LOAD [Start Date],
[End Date],
ProductId
Resident Table2;
Left Join (Table1)
LOAD *
Resident Table2;
Right Join (Table1)
LOAD ProductId,
Min(Price_Alt) as Price_Alt
Resident Table1
Group By ProductId;
DROP Table Table2;
That did the Job....Thanks Sunny!!!