IntervalMatch + Primary Key Question

[reycarloluber]

Hello. I am fairly new to Qlik Sense and am having a problem with Interval Match with a Primary Key. 

I was looking for answers online and tried to implement it using this code:

Employee:

LOAD * Inline [

EmpId,     	EmpName,           	StartDate,         EndDate

101,     	'John Doe',     	'01/01/2018',      '31/12/2018'

101,     	'Jane Doe',     	'01/01/2019',      '31/12/2019'

]
;


Leave:

LOAD * Inline [

LeaveId,     EmpId,			LeaveType,			LeaveStart,		LeaveEnd

1,     		101,			'Annual Leave',     '14/01/2018',  '14/01/2018'           

2,     		101,			'Sick Leave',       '15/04/2019',  '15/04/2019'         

];



Left Join (Leave)

IntervalMatch(LeaveStart,EmpId)

LOAD StartDate,

EndDate,

EmpId

Resident Employee
;


Left Join (Leave)

LOAD *

Resident Employee;
Drop Table Employee;

I've also tried the slowly changing dimensions guide which is this: 

tmpEmployee:

LOAD * Inline [

EmpId,     	EmpName,           	StartDate,         EndDate

101,     	'John Doe',     	'01/01/2018',      '31/12/2018'

101,     	'Jane Doe',     	'01/01/2019',      '31/12/2019'

]
;



tmpLeave:

LOAD * Inline [

LeaveId,     EmpId,			LeaveType,			LeaveStart,		LeaveEnd

1,     		101,			'Annual Leave',     '14/01/2018',  '14/01/2018'           

2,     		101,			'Sick Leave',       '15/04/2019',  '15/04/2019'         

];


Employee:
Load
	EmpId,
    EmpName,
    StartDate,
    EndDate,
    EmpId &'|'& StartDate &'|'& EndDate as [EmpId+Interval]
Resident tmpEmployee;
Drop Table tmpEmployee;

Leave:
Load
	LeaveId,
    EmpId as TmpEmpId,
    LeaveType,
    LeaveStart,
    LeaveEnd,
    EmpId &'|'& LeaveStart as [EmpId+LeaveStart] 
Resident tmpLeave;
Drop Table tmpLeave;

TmpBridgeTable: 
IntervalMatch (LeaveStart,TmpEmpId)  
Load distinct StartDate, EndDate, EmpId as TmpEmpId 
Resident Employee; 

Left Join (Leave)
Load 
	TmpEmpId, 
    LeaveStart,
    TmpEmpId & '|' & StartDate & '|' & EndDate as [EmpId+Interval]
Resident TmpBridgeTable;


// BridgeTable: 
// Load 
// TmpEmpId & '|' & LeaveStart as [EmpId+LeaveStart],  
// TmpEmpId & '|' & StartDate & '|' & EndDate as [EmpId+Interval]
// Resident TmpBridgeTable; 

Drop Field TmpEmpId;
Drop Table TmpBridgeTable;

However, both codes aren't working as this is the output that I was expecting:

As a context, EmpId are the same for both and EmpName is different based on a certain timeframe (Start and End Date). The EmpName that will reflect on the output is the one where Leave Start is between StartDate and EndDate.

It's easy to implement this in sql however, there might be a scenario where we need to work with csv in which case sql will not be available, as far as I know.

I would really appreciate your inputs on where I got it wrong and what is the correct way of implementing this.

Thank you!

Sincerely,

Rey

[Nicole-Smith]

This code:

Employee_Temp:
LOAD * INLINE [
	EmpId, EmpName, StartDate, EndDate
	101, John Doe, 01/01/2018, 31/12/2018
	101, Jane Doe, 01/01/2019, 31/12/2019
];


Leave:
LOAD * INLINE [
	LeaveId, EmpId, LeaveType, LeaveStart, LeaveEnd
	1, 101, Annual Leave, 14/01/2018, 14/01/2018
	2, 101, Sick Leave, 15/04/2019, 15/04/2019
];

Employee:
IntervalMatch(LeaveStart, EmpId)
LOAD StartDate, EndDate, EmpId
RESIDENT Employee_Temp;

LEFT JOIN (Employee)
LOAD *
RESIDENT Employee_Temp;

DROP TABLE Employee_Temp;

Will return this output on a front-end table:

EmpId	EmpName	LeaveId	LeaveType	LeaveStart	LeaveEnd
101	John Doe	1	Annual Leave	14/01/2018	14/01/2018
101	Jane Doe	2	Sick Leave	15/04/2019	15/04/2019

You can keep the synthetic key in the script because that's what the IntervalMatch() function is supposed to create to link these tables together.

You were pretty close with your first attempt, it just needed a couple tweaks.

[reycarloluber]

Hi Nicole,

Thank you for your reply! I tried using your script. However, I'm still getting a wrong output.

Am I doing anything wrong?

Sincerely,

Rey

[Nicole-Smith]

Without seeing your actual file, it's hard to tell what you're doing wrong.

I've attached my working example file so you can compare to your file.  My output looks like you wanted using the same code from my previous post:

[reycarloluber]

Oh right. Sorry about that. I have attached the qvf file now.

This is generated from Qlik Sense Cloud (the free one). I don't have access to a Qlik Sense Server outside office hours. However, I get the same result from both instances.

[Nicole-Smith · Accepted solution]

The dates you have in your example data are formatted DD/MM/YYYY but the variables you have set in the main tab say the dates are formatted M/D/YYYY:

If you update those to variables to DD/MM/YYYY, then it should work (this is how it's set up in my example file).

[reycarloluber]

Oh wow! Thank you very much! I have never considered looking there because it's auto generated and my date field seems to be fine when creating reports. Now I know why other "Solved" questions didn't work on my end when I tried them out.

I should start factoring these things moving forward. Thank you again, Nicole!