Need to identify consecutive work days

[#1]

Hi,

I have a set of data that shows value per person claimed over a range of days.  Sometimes these have been entered one line of data per day and sometimes they have been entered as a date range of a week or more.

I need to identify where someone has made a claim for more than five consecutive working days but I am struggling to figure out how to identify this.

I've attached a simplified example of the type of information we have and am hoping that someone might be able to help?  I did think about trying to expand the data so that we had a flag for each day to say whether there was a claim made or not, but I couldn't work out how to get from the date ranges (start and end date) to a full list of dates in that range.

Any ideas guys?

Thanks,

Emma

[ramoncova06]

I haven't seen the document but, you can get the dates ranges by using intervalmatch

IntervalMatch

[m_woolf · Accepted solution]

Open this link and download the pdf. Then look at the Example: Creating one record per day that a contract is valid

Generating Missing Data In QlikView

[Anonymous]

See if NetWorkDays() helps

[Anonymous]

Like this?

[#6]

So I think I've managed to get pretty much what I need as a combination of ideas.

The problem I had is that each person may have several claims and the data is a mix of people who have submitted a date range and others that have submitted one claim per day.

This might not end up being the final version I use, however it is a huge step closer.  many thanks fo everyone for sending their ideas!

[#7]

Hi,

This was really close and sent me in the right direction - there is an issue if users have multiple claims which are not consecutive though.

Thanks,

Emma

[#8]

Hi Michael,

Unfortunately NetWorkDays will not work for this.  I need to be able to idenify a number of days over several rows of data.

Thanks,

Emma