Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

14 Replies
deepakqlikview_123
Specialist
Specialist

u should rename field.

Always remember in interval match one key should be match field(Comman field between 2 table) and one key should be key  field to match with interval that is upper and lower bound.

thanks,

Deepak

Not applicable
Author

Hi Deepak,

In this situation then i dont have any common field, renaming the field will not help since both the id's hold different data.

Thanks,

Sonali

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

This is a classic slowly changing dimension, and you would load it like this (assuming table 1 in your post is named Lookup and table 2 is named Data):

// Perform interval match by employee ID

Left Join (Data)

IntervalMatch([Transaction Date], ID)

LOAD [Start Date],

  [End Date],

  [Emp ID] As ID

Resident Lookup;

// Bring location into Data table and clean up

Left Join (Data)

LOAD [Start Date],

  [End Date],

  [Emp ID] As ID,

  Location

Resident Lookup;

DROP Table Lookup;

See attached, which also shows one way to handle the null end dates.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Jonathan,

Thanks for your quick reply.

I have one more situation where there is no id field in the 2nd table.

Table 1 with emp details

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 2 has only 2 columns

Transaction DateBusiness Flag
1-Jan-Y
02-Jan-2010Y
03-Jan-2010N

How can i now select the transaction dates between the start date and end date

can you help?

Not applicable
Author

Hi Sonali, Simply use the Basic IntervelMatch function if you don't have the key.

Data:

LOAD EMP_ID, [Start Date], [End Date], [Location ID] From Table1;

Transaction:

LOAD [Transaction Date], [Business Flag] From Table1;

Left Join (Data)

IntervalMatch( [Transaction Date])

LOAD [Start Date] , [End Date] Resident Data ;