How to use Interval Match on 2 tables?

[rishimessi19]

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:

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

[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]

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?

[sunny_talwar · Accepted solution]

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]

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

[rishimessi19]

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.