Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
First off, let me say I am new to the community and QV in general. I went through the basic training and I have a background with similar applications and programming languages. Now with that said....
My Task is to flag potential overlapping dates based on their ID. Here are my fields:
ID
Start Date
End Date
In my original dataset, the ID field could be duplicated but would have different dates. Take the following example:
Record 1
Id = 1
Start Date = 11/1/2014
End Date = 11/19/2014
Record 2
Id = 1
Start Date = 11/5/2014
End Date = 11/8/2014
I need to flag the dataset if my start date in record 2 or more is before End Date of record 1 AND ( equal to or later than Start Date of record 1) but only if the Id's are the same. So in the scenario above I would need to set a flag, but if recordset 2 had a different Id then Recordset 1 no flag would be set.
Any thoughts on how I might accomplish this?
Thanks!
Something like this:
Temp:
LOAD * FROM ...source...;
Result:
NOCONCATENATE
LOAD
Id,
[Start Date],
[End Date],
if( (Previous(Id)= Id and ([Start Date] >= previous([Start Date]) and [End Date] <= previous([End Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([End Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([Start Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([End Date]) and [End Date] >= previous([End Date]) ) , 1, 0) as Flag
RESIDENT Temp
ORDER BY Id, [Start Date]
;
DROP TABLE Temp;
Something like this:
Temp:
LOAD * FROM ...source...;
Result:
NOCONCATENATE
LOAD
Id,
[Start Date],
[End Date],
if( (Previous(Id)= Id and ([Start Date] >= previous([Start Date]) and [End Date] <= previous([End Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([End Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([Start Date]) and [End Date] >= previous([Start Date]) ) or
(Previous(Id)= Id and [Start Date] <= previous([End Date]) and [End Date] >= previous([End Date]) ) , 1, 0) as Flag
RESIDENT Temp
ORDER BY Id, [Start Date]
;
DROP TABLE Temp;
@Gysbert, Thanks!
I didnt know about the previous command. That is exactly what I was looking to do.