Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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 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 2 has only 2 columns
Transaction Date | Business Flag |
---|---|
1-Jan- | Y |
02-Jan-2010 | Y |
03-Jan-2010 | N |
How can i now select the transaction dates between the start date and end date
can you help?
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 ;