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: 
rishimessi19
Contributor II
Contributor II

How to use Interval Match on 2 tables?

Hi

I have the following 2 tables in which each record has a Start date and End Date. How do I create a data model with these 2 tables? The requirement is to have a date filter and display the Emp_NM, Salary & Manager_NM in a Straight table on UI.

 

Emp_Manager table:

EmpIDEmp_NMManagerIDManager_NMStart_DateEnd_Date
1John Smith76Leo Maguire1/1/20149/30/2015
1John Smith77Trace Young10/1/201512/31/2099
2Paul Lin98Eric Dwight1/1/20141/31/2016
2Paul Lin99Tom Luff2/1/201610/31/2018
2Paul Lin100Karen James11/1/201812/31/2099

 

Emp_Salary table:

EmpIDEmp_NMSalaryStart_DateEnd_Date
1John Smith900001/1/201412/31/2014
1John Smith1000001/1/20157/31/2018
1John Smith1200008/1/201812/31/2099
2Paul Lin1900001/1/20148/31/2015
2Paul Lin2000009/1/20155/31/2018
2Paul Lin2200006/1/201812/31/2099
1 Solution

Accepted Solutions
sunny_talwar

This is an alternative

Emp_Manager:
LOAD * INLINE [
    EmpID, Emp_NM, ManagerID, Manager_NM, Start_Date, End_Date
    1, John Smith, 76, Leo Maguire, 1/1/2014, 9/30/2015
    1, John Smith, 77, Trace Young, 10/1/2015, 12/31/2099
    2, Paul Lin, 98, Eric Dwight, 1/1/2014, 1/31/2016
    2, Paul Lin, 99, Tom Luff, 2/1/2016, 10/31/2018
    2, Paul Lin, 100, Karen James, 11/1/2018, 12/31/2099
];

Calendar:
LOAD Date(MakeDate(2014, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2014, 1, 1) + IterNo() - 1 <= Today();

Left Join (Emp_Manager)
IntervalMatch(Date)
LOAD Start_Date,
	 End_Date
Resident Emp_Manager;

Left Join (Emp_Manager)
LOAD *
Resident Calendar;

DROP Fields Start_Date, End_Date;

Emp_Salary:
LOAD * INLINE [
    EmpID, Emp_NM, Salary, Start_Date, End_Date
    1, John Smith, 90000, 1/1/2014, 12/31/2014
    1, John Smith, 100000, 1/1/2015, 7/31/2018
    1, John Smith, 120000, 8/1/2018, 12/31/2099
    2, Paul Lin, 190000, 1/1/2014, 8/31/2015
    2, Paul Lin, 200000, 9/1/2015, 5/31/2018
    2, Paul Lin, 220000, 6/1/2018, 12/31/2099
];

Calendar:
LOAD Date(MakeDate(2014, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2014, 1, 1) + IterNo() - 1 <= Today();

Left Join (Emp_Salary)
IntervalMatch(Date)
LOAD Start_Date,
	 End_Date
Resident Emp_Salary;

Left Join (Emp_Salary)
LOAD *
Resident Calendar;

DROP Fields Start_Date, End_Date;

Emp:
NoConcatenate
LOAD *
Resident Emp_Manager;

Left Join (Emp)
LOAD *
Resident Emp_Salary;

DROP Tables Emp_Manager, Emp_Salary;

View solution in original post

6 Replies
sunny_talwar

There are two options which you can try

1) Create a link table from the two tables and then use intervalmatch to join it to your calendar table

2) You can concatenate the two tables into a single table and then use intervalmatch.

sunny_talwar

Listing out the second example

Emp:
LOAD * INLINE [
    EmpID, Emp_NM, ManagerID, Manager_NM, Start_Date, End_Date
    1, John Smith, 76, Leo Maguire, 1/1/2014, 9/30/2015
    1, John Smith, 77, Trace Young, 10/1/2015, 12/31/2099
    2, Paul Lin, 98, Eric Dwight, 1/1/2014, 1/31/2016
    2, Paul Lin, 99, Tom Luff, 2/1/2016, 10/31/2018
    2, Paul Lin, 100, Karen James, 11/1/2018, 12/31/2099
];

Concatenate (Emp)
LOAD * INLINE [
    EmpID, Emp_NM, Salary, Start_Date, End_Date
    1, John Smith, 90000, 1/1/2014, 12/31/2014
    1, John Smith, 100000, 1/1/2015, 7/31/2018
    1, John Smith, 120000, 8/1/2018, 12/31/2099
    2, Paul Lin, 190000, 1/1/2014, 8/31/2015
    2, Paul Lin, 200000, 9/1/2015, 5/31/2018
    2, Paul Lin, 220000, 6/1/2018, 12/31/2099
];

Calendar:
LOAD Date(MakeDate(2014, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2014, 1, 1) + IterNo() - 1 <= Today();

IntervalMatch:
IntervalMatch(Date)
LOAD Start_Date,
	 End_Date
Resident Emp;
rishimessi19
Contributor II
Contributor II
Author

Hi Sunny

 

Thank you for responding.

If I implement your solution, I do not get the desired result. See exhibit below:Capture.PNG

 
 

I am looking for a solution where I do not get the null values as shown above.

Do you have any alternate solution?

sunny_talwar

This is an alternative

Emp_Manager:
LOAD * INLINE [
    EmpID, Emp_NM, ManagerID, Manager_NM, Start_Date, End_Date
    1, John Smith, 76, Leo Maguire, 1/1/2014, 9/30/2015
    1, John Smith, 77, Trace Young, 10/1/2015, 12/31/2099
    2, Paul Lin, 98, Eric Dwight, 1/1/2014, 1/31/2016
    2, Paul Lin, 99, Tom Luff, 2/1/2016, 10/31/2018
    2, Paul Lin, 100, Karen James, 11/1/2018, 12/31/2099
];

Calendar:
LOAD Date(MakeDate(2014, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2014, 1, 1) + IterNo() - 1 <= Today();

Left Join (Emp_Manager)
IntervalMatch(Date)
LOAD Start_Date,
	 End_Date
Resident Emp_Manager;

Left Join (Emp_Manager)
LOAD *
Resident Calendar;

DROP Fields Start_Date, End_Date;

Emp_Salary:
LOAD * INLINE [
    EmpID, Emp_NM, Salary, Start_Date, End_Date
    1, John Smith, 90000, 1/1/2014, 12/31/2014
    1, John Smith, 100000, 1/1/2015, 7/31/2018
    1, John Smith, 120000, 8/1/2018, 12/31/2099
    2, Paul Lin, 190000, 1/1/2014, 8/31/2015
    2, Paul Lin, 200000, 9/1/2015, 5/31/2018
    2, Paul Lin, 220000, 6/1/2018, 12/31/2099
];

Calendar:
LOAD Date(MakeDate(2014, 1, 1) + IterNo() - 1) as Date
AutoGenerate 1
While MakeDate(2014, 1, 1) + IterNo() - 1 <= Today();

Left Join (Emp_Salary)
IntervalMatch(Date)
LOAD Start_Date,
	 End_Date
Resident Emp_Salary;

Left Join (Emp_Salary)
LOAD *
Resident Calendar;

DROP Fields Start_Date, End_Date;

Emp:
NoConcatenate
LOAD *
Resident Emp_Manager;

Left Join (Emp)
LOAD *
Resident Emp_Salary;

DROP Tables Emp_Manager, Emp_Salary;
Brett_Bleess
Former Employee
Former Employee

Rishi, did Sunny's alternative do the trick for you?  If so, do not forget to come back to the thread and use the Accept as Solution button on his last post to mark that as the solution to give him credit for the help and let other Community Members know that it did work for you.  If you are still working upon things, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
rishimessi19
Contributor II
Contributor II
Author

Hi Sunny

Your updated solution works. 

One thing though, your script is creating one row for each employee & date combination. Is there a better solution to avoid this?

I will still go ahead and mark this as a correct solution.