Joining the Intervalmatch output with main query

[#1]

Hello All,

While optimizing the SQL query in QlikView, i have come across a situation where i have to use intervalmatch for comparing dates from 2 tables which are not linked using any field and need to inner/left join the intervalmatch output with the main query.

For eg

Table A has 4 columns

Emp ID	Start Date	End Date	Location ID
1	01-Mar-2011	null()	1
2	01-Jan-2012	23-Dec-2013	2
3	21-May-2014	null()	2
4	01-Apr-2011	null()	3
5	30-Mar-2012	30-Mar-2014	5

Table B has 3 Columns basically a table with all the transaction dates from 1990 till year 2019 capturing all the transactions posted to GL

ID	Transaction Date	Business Flag
1	01-Jan-2011	Y
2	02-Jan-2010	Y
3	03-Jan-2010	N

Both the above tables are not linked using any ID's, using interval match i need to find out the location id's of employees where the Transaction dates from table B  are between the start date and end date of table A.

Transaction Date >= Start date and Transaction Date <= End Date.

Once i get this output i need to left join it with the main query which has other employee details. How to left join this output in the main query Can anybody help?

Thanks,

Sonali

[deepakqlikview_123]

Hi Sonali,

You have time interval in your first table u need tp match ur keyfield (TranactiponDate)from your second table to Start date end date in your first tablle using interval match function.

In interval match table u should have 3 fields

TranactiponDate

Start date

end date

after creation of interval match table u can either join with ur fact table or keep it separated.

Below table u need to do conditional formatting like

Transaction Date >= Start date and Transaction Date <= End Date.

Till now I never seen null values in End date.I never handle such situation

thanks,

[#3]

Basically the end date is a dummy future date created we are replacing the null values with the dummy date like 31-dec-5015

[deepakqlikview_123]

Hi Sonali,

PFA.

You can refer this doc and use interval match function.

And use conditions whatever u want after usage of interval match function.

thanks,

Deepak

[#5]

Thanks Deepak,

I will go through it and try out the possibilities.

[#6]

Hi,

Not a single Transaction Date is satisfying condition Transaction Date >= Start date and Transaction Date <= End Date.

Can u clarify this ??

[#7]

Hi Deepak,

Thanks for sharing the document, but as per the example you shared both tables have common key as employee id and thats how left join can be used, in my situation i don't have a common key between the 2 tables and i have to join them after using intervalmatch.

Thanks,

Sonali

[deepakqlikview_123]

Rename id from second table as EmpId.Because ID is unique. proceed for same.

[#9]

Hi Vivek,

The transaction date table here will have every single year's day starting from 01-Jan-1989 till 31-Dec-2019.

Hope that is clear now.

Thanks,

Sonali

[#10]

Hi Deepak,

Both Id's are not same, one is an emp id and another is the transaction id.

Regards,

Sonali