inner join tables between date range

[#1]

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?

[nagaiank]

intervalmatch will help.

[sujeetsingh]

You can use Apply map too.

For this..

[sujeetsingh]

You can also try Left Keep

follow this

Understanding Join, Keep and Concatenate

[SunilChauhan]

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

[#6]

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

[MarcoWedel · Accepted solution]

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

[#8]

Thank you. IntervalMatch worked.

[MarcoWedel]

you're welcome

regards

Marco

[rohitians]

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