topic interval match question in QlikView

interval match question

Wed, 25 Nov 2020 16:16:04 GMT

Here's my data...

ID	start	end	other
ABCD	1/12/2017	1/24/2017	...
ABCD	1/23/2017	1/24/2017	...
ABCD	1/24/2017	3/1/2017	...

If I was using the date 1/24/2017, how do I retrieve the last record only? I'm using IntervalMatch in my script now and everything works great unless the specific date is exactly the same for multiple rows like this. For example if I was using 1/25/2017 I'm getting the last row correctly.

Thanks.

Re: interval match question

el_aprendiz111 — Wed, 12 Jul 2017 16:09:50 GMT

Hi,

IntervalMatch

Re: interval match question

Wed, 12 Jul 2017 18:55:17 GMT

Hi thanks for the link. I am already using interval match. Does it allow for stipulating as I need?

The following is a simple example of what's happening... I get 3 rows back for test date of 1/24/2017... I only want the last row with EndDate of 3/1/2017

TestDataTable:

Load * Inline [
EmpID, TestDate, TestName
ABCD, 1/24/2017, x
XYZQ, 2/05/2017, y
];

EmpData:

Load * Inline [
EmpID, StartDate, EndDate, OtherField
ABCD, 1/01/2017, 1/12/2017, x
ABCD, 1/12/2017, 1/24/2017, x
ABCD, 1/23/2017, 1/24/2017, x
ABCD, 1/24/2017, 3/1/2017, x
];

left Join (TestDataTable)
IntervalMatch(TestDate, EmpID)
LOAD StartDate,
EndDate,
EmpID
Resident EmpData;

Left Join (TestDataTable)
LOAD *
Resident EmpData;

DROP Table EmpData;