Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;