Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Joining the Intervalmatch output with main query

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 IDStart DateEnd DateLocation ID
101-Mar-2011null()1
201-Jan-201223-Dec-20132
321-May-2014null()2
401-Apr-2011null()3
530-Mar-201230-Mar-20145

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

IDTransaction DateBusiness Flag
101-Jan-2011Y
202-Jan-2010Y
303-Jan-2010N

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

Tags (1)
14 Replies
deepaktibhe
Valued Contributor

Re: Joining the Intervalmatch output with main query

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,

Not applicable

Re: Joining the Intervalmatch output with main query

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

deepaktibhe
Valued Contributor

Re: Re: Joining the Intervalmatch output with main query

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

Not applicable

Re: Joining the Intervalmatch output with main query

Thanks Deepak,

I will go through it and try out the possibilities.

Not applicable

Re: Joining the Intervalmatch output with main query

Hi,

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

Can u clarify this ??

Not applicable

Re: Joining the Intervalmatch output with main query

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

deepaktibhe
Valued Contributor

Re: Joining the Intervalmatch output with main query

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

Not applicable

Re: Joining the Intervalmatch output with main query

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

Not applicable

Re: Joining the Intervalmatch output with main query

Hi Deepak,

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

Regards,

Sonali

Community Browser