Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
reycarloluber
New Contributor II

IntervalMatch + Primary Key Question

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:

expectedoutput.PNG

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

1 Solution

Accepted Solutions

Re: IntervalMatch + Primary Key Question

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:Untitled.png

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).

6 Replies

Re: IntervalMatch + Primary Key Question

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 
101John Doe1Annual Leave14/01/201814/01/2018
101Jane Doe2Sick Leave15/04/201915/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
New Contributor II

Re: IntervalMatch + Primary Key Question

Hi Nicole,

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

 

currentoutput.PNG

Am I doing anything wrong?

Sincerely,

Rey

Re: IntervalMatch + Primary Key Question

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:

Capture.JPG

reycarloluber
New Contributor II

Re: IntervalMatch + Primary Key Question

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.

Re: IntervalMatch + Primary Key Question

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:Untitled.png

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
New Contributor II

Re: IntervalMatch + Primary Key Question

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!