topic How to use Interval Match on 2 tables? in QlikView

How to use Interval Match on 2 tables?

rishimessi19 — Sat, 16 Nov 2024 01:57:21 GMT

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:

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

Emp_Salary table:

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

Re: How to use Interval Match on 2 tables?

sunny_talwar — Wed, 13 Nov 2019 19:46:50 GMT

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.

Re: How to use Interval Match on 2 tables?

sunny_talwar — Wed, 13 Nov 2019 19:50:18 GMT

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;

Re: How to use Interval Match on 2 tables?

rishimessi19 — Wed, 13 Nov 2019 21:15:13 GMT

Hi Sunny

Thank you for responding.

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

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

Do you have any alternate solution?

Re: How to use Interval Match on 2 tables?

sunny_talwar — Thu, 14 Nov 2019 12:13:25 GMT

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;

Re: How to use Interval Match on 2 tables?

Brett_Bleess — Mon, 18 Nov 2019 19:13:23 GMT

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

Re: How to use Interval Match on 2 tables?

rishimessi19 — Fri, 22 Nov 2019 16:28:23 GMT

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.