Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
we have two below tables
Table 1:
Item | Tariff | Start Date | End Date |
N001 | 50 | 1-Jan-12 | 1-May-12 |
N001 | 55 | 1-May-12 | 31-Dec-99 |
Table 2:
Item | MRDate | Qty |
N001 | 3-Jan-12 | 5 |
N001 | 4-Feb-12 | 9 |
N001 | 6-May-12 | 20 |
Table 1 is tariff table and our tariff is expired on specific time and table 2 is our transnational table.
We want to join them on the base of Item and MRdate which means that MRDate must lie between Start Date and End Date of Table2.
If I create there query on SQL that is like { select a.Item, a.MRDate, a.Qty, b.Tariff from Table1 A join Table2 B on (a.item = b.item and a.MRDate between b."Start Date" and "End Date") }
Please tell me how is it possible on qlikview.
Our Result like that....
Item | MRDate | Qty | Tarif |
N001 | 3-Jan-12 | 5 | 50 |
N001 | 4-Feb-12 | 9 | 50 |
N001 | 6-May-12 | 20 | 55 |
Thank You
Hi,
you should use intervalmatch for this;
join intervalmatch (MRDate, Item) load StartDate, EndDate, Item resident TariffTable;
LEFT JOIN (Transactions) load * RESIDENT TariffTable;
DROP TABLE TariffTable;
Hope this helps!
Try IntervalMatch()
Hi,
you should use intervalmatch for this;
join intervalmatch (MRDate, Item) load StartDate, EndDate, Item resident TariffTable;
LEFT JOIN (Transactions) load * RESIDENT TariffTable;
DROP TABLE TariffTable;
Hope this helps!
Great Thanks thats working......