topic Re: Need to identify consecutive work days in QlikView

Need to identify consecutive work days

Tue, 21 Apr 2015 16:14:48 GMT

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

Re: Need to identify consecutive work days

ramoncova06 — Tue, 21 Apr 2015 16:37:36 GMT

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

IntervalMatch

Re: Need to identify consecutive work days

m_woolf — Tue, 21 Apr 2015 16:41:25 GMT

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

Re: Need to identify consecutive work days

Anonymous — Tue, 21 Apr 2015 16:55:47 GMT

See if NetWorkDays() helps

Re: Need to identify consecutive work days

Anonymous — Tue, 21 Apr 2015 17:42:49 GMT

Like this?

Re: Need to identify consecutive work days

Wed, 22 Apr 2015 12:59:08 GMT

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!

Re: Need to identify consecutive work days

Wed, 22 Apr 2015 13:01:04 GMT

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

Re: Need to identify consecutive work days

Wed, 22 Apr 2015 13:02:55 GMT

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