topic Re: Flagging records with overlapping dates in QlikView

Flagging records with overlapping dates

cbushey1 — Thu, 20 Nov 2014 16:11:44 GMT

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:

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!

Re: Flagging records with overlapping dates

Gysbert_Wassenaar — Fri, 21 Nov 2014 14:50:42 GMT

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;

Re: Flagging records with overlapping dates

cbushey1 — Fri, 21 Nov 2014 15:06:37 GMT

@Gysbert, Thanks!

I didnt know about the previous command. That is exactly what I was looking to do.