topic Re: Conditional Joining in Qlikview in QlikView

Conditional Joining in Qlikview

Wed, 06 Nov 2013 08:30:18 GMT

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

Re: Conditional Joining in Qlikview

tresB — Wed, 06 Nov 2013 08:45:16 GMT

Try IntervalMatch()

Re: Conditional Joining in Qlikview

salto — Wed, 06 Nov 2013 08:59:56 GMT

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!

Re: Conditional Joining in Qlikview

Wed, 06 Nov 2013 09:56:40 GMT

Great Thanks thats working......