topic Re: inner join tables between date range in QlikView

inner join tables between date range

Fri, 20 Nov 2020 18:02:30 GMT

How can I accomplish inner joining two tables between a date range in Qlikview. In SQL this is possible with something like:

INNER JOIN TableA ON (TableA.Dates BETWEEN TableB.Start_Date AND TableB.End_Date)

In Qlikview I have something like this:

DatesData:

LOAD * Inline [

Test_Date

11/1/2013

12/1/2013

1/1/2014

2/1/2014

3/1/2014

4/1/2014

5/1/2014

];

PersonData:

LOAD * Inline [

ID, Start_Date, End_Date

1, 12/1/2013, 2/1/2014

2, 1/1/2013, 3/1/2014

3, 2/1/2014, 4/1/2014

];

And I need to create a table like this:

ID, Dates

1, 12/1/2013

1, 1/1/2014

1, 2/1/2014

2, 1/1/2014

2, 2/1/2014

2, 3/1/2014

etc.....

How can I accomplish a join like this in Qlikview?

Re: inner join tables between date range

nagaiank — Wed, 27 Aug 2014 02:37:55 GMT

intervalmatch will help.

Re: inner join tables between date range

sujeetsingh — Wed, 27 Aug 2014 03:57:41 GMT

You can use Apply map too.

For this..

Re: inner join tables between date range

sujeetsingh — Wed, 27 Aug 2014 03:58:57 GMT

You can also try Left Keep

follow this

Understanding Join, Keep and Concatenate

Re: inner join tables between date range

SunilChauhan — Wed, 27 Aug 2014 04:34:39 GMT

DatesData:

LOAD * Inline [

Test_Date

11/1/2013

12/1/2013

1/1/2014

2/1/2014

3/1/2014

4/1/2014

5/1/2014

];

PersonDataTemp:

LOAD * Inline [

ID, Start_Date, End_Date

1, 12/1/2013, 2/1/2014

2, 1/1/2013, 3/1/2014

3, 2/1/2014, 4/1/2014

];

PersonData:

Load

ID, Start_Date as Test_Date

resident PersonDataTemp;

Concatenate(PersonData)

Load

ID, End_Date as Test_Date

resident PersonDataTemp;

drop table PersonData;

hope this helps

Re: inner join tables between date range

Wed, 27 Aug 2014 10:43:47 GMT

Hi Dorian

you can visit this given URL and get the exact idea about inner joins in Qlikview application. There will be different syntax in every Database.

http://www.resultdata.com/using-joins-in-qlikview/

as you can see many example are given in this URL.

Also you can find very good example from below URL

http://qlikshare.com/898/

Thanks

Saumil Jani

Re: inner join tables between date range

MarcoWedel — Wed, 27 Aug 2014 16:19:54 GMT

Hi,

one solution:

DatesData:

LOAD * Inline [

Test_Date

11/1/2013

12/1/2013

1/1/2014

2/1/2014

3/1/2014

4/1/2014

5/1/2014

];

PersonData:

LOAD * Inline [

ID, Start_Date, End_Date

1, 12/1/2013, 2/1/2014

2, 1/1/2013, 3/1/2014

3, 2/1/2014, 4/1/2014

];

tabMatch:

IntervalMatch (Test_Date)

LOAD Start_Date, End_Date

Resident PersonData;

hope this helps

regards

Marco

Re: inner join tables between date range

Thu, 28 Aug 2014 13:29:57 GMT

Thank you. IntervalMatch worked.

Re: inner join tables between date range

MarcoWedel — Thu, 28 Aug 2014 13:33:26 GMT

you're welcome

regards

Marco

Re: inner join tables between date range

rohitians — Tue, 16 Aug 2016 06:38:49 GMT

Dear Marco,

I have used the same solution as given by you,

but we need to avoid synthetic key right .

Can you explain??

Thanks,

Rohit