topic Re: Find dates in QlikView

Find dates

qvqfqlik — Fri, 07 Apr 2017 21:48:00 GMT

I need to count the number of days consecutively based on the list of dates in the data(irrespective of any holidays) .

For the "counter" Iam getting just one value '1'.

Re: Find dates

qvqfqlik — Sun, 09 Apr 2017 07:05:10 GMT

Please help with finding consecutive dates

Re: Find dates

Anonymous — Sun, 09 Apr 2017 07:10:23 GMT

How about this ?? How to calculate consecutive work days?

Re: Find dates

qvqfqlik — Sun, 09 Apr 2017 07:13:57 GMT

I tried it, it wasn't working for me. Can you please help or correct the script I posted

Re: Find dates

Anonymous — Sun, 09 Apr 2017 12:56:16 GMT

Can you attach the data file, that may help to understand the problem better.

Regards

Bhuvan Agarwal

Re: Find dates

Anil_Babu_Samineni — Mon, 10 Apr 2017 03:30:45 GMT

Would you share excel data and expected result please? Because first loaded time you used hard coded date. We are not getting it the use

Re: Find dates

qvqfqlik — Mon, 10 Apr 2017 04:47:58 GMT

I have attached the file to my original post.

I need to find employees who worked for more than 10 consecutive dates.

I don't have the count of no.of consecutive days of work in the data source.

I need to find the count of consecutive dates from the list of dates an employee has worked.

Iam looking for something like this How to calculate consecutive work days?

Re: Find dates

Anil_Babu_Samineni — Mon, 10 Apr 2017 05:09:32 GMT

I never worked with it, That is the case Gysbert Wassenaar can help you.

Re: Find dates

MK_QSL — Mon, 10 Apr 2017 06:05:34 GMT

1) This can not be achieved (or say very difficult) to do at front end. We have to achieve the same using Script. For that you have to provide sample (dummy) data.

2) If you can't provide dummy data then you can try How to calculate consecutive work days?.

Re: Find dates

qvqfqlik — Mon, 10 Apr 2017 14:44:44 GMT

I have attached sample data and the error to my post. Please help me to correct this.

I need to count the number of days consecutively based on the list of dates in the data(irrespective of any holidays) .

I have written sum() for the sake of writing group by like in the other thread.

Re: Find dates

Miguel_Angel_Baeyens — Mon, 10 Apr 2017 15:06:52 GMT

Did you try first loading everything from the different files and then either store the results on a QVD and using the GROUP BY there or doing a RESIDENT load?

Then you can apply the same logic than in the post as Gysbert explains.

Re: Find dates

qvqfqlik — Mon, 10 Apr 2017 15:52:10 GMT

Iam using resident load. No QVDs

I need to count the number of days consecutively based on the list of dates in the data(irrespective of any holidays) .

For the "counter" Iam getting just one value '1'.

I have updated my workfile. Ihave removed group by.

I am not sure if Iam missing something. can you please help with this

Re: Find dates

qvqfqlik — Mon, 10 Apr 2017 17:56:34 GMT

stalwar1 can you please help with this

Re: Find dates

MK_QSL — Tue, 11 Apr 2017 05:56:13 GMT

Data:
Load 
  *,
  If([Employee Name] <> previous([Employee Name]),1,
  IF(DATE-1=Previous(DATE),RangeSum(Peek('Counter'),1),1)) as Counter
Resident Work order by [Employee Name],DATE;


Drop table Work;

Re: Find dates

qvqfqlik — Tue, 11 Apr 2017 15:03:05 GMT

I have attached updated work files.

when I make selections, Iam getting incorrect results.

if I select a counter value, it will not show correct results as the employee name and the consecutive dates that they worked on.

there are no consecutive dates, some of the dates in between are missing .

Please help with this issue

Re: Find dates

MK_QSL — Wed, 12 Apr 2017 07:52:26 GMT

1) NewFlag_14DaysCounter is having Value 1 and 0. 1 will give you employee worked >=15 Days consecutively.

2) Select 1 from this list box. This will filter Counter having values >=15 days.

3) This will also filter Employee ID respectively.

4) Select any Employee ID.. i.e. 5666.

5) Table box now will show you data correspond to this Employee. You can see that his emplyee worked between 11/15/2016 to 12/2/2016 (18 days).

6) I have also created FromDate and ToDate which will give you information when the consecutive work days start and finish.

HTH.

Regards,

Temp:

Load

[Employee ID],

DATE,

if(rowno()=1 or [Employee ID] <> previous([Employee ID]) ,1,

if(DATE-previous(DATE)=1,RangeSum(Peek('Counter'),1),1)) as Counter,

if(rowno()=1 or [Employee ID] <> previous([Employee ID]) or (DATE - Previous(DATE))<>1,DATE,PEEK(TEMPDATE)) as TEMPDATE

Resident Kronos

order by [Employee ID],DATE;

Temp2:

Load *, AutoNumber([Employee ID] & TEMPDATE, 'TEMPDATE') as Flag Resident Temp;

Drop Table Temp;

Temp3:

Load

[Employee ID],

Flag,

Max(Counter) as Counter,

Date(Min(DATE)) as FromDate,

Date(Max(DATE)) as ToDate

Resident Temp2

Group By Flag, [Employee ID];

Left Join(Temp3)

Load [Employee ID], Flag, Max(Counter) as MaxCounter Resident Temp3 Group By [Employee ID],Flag;

Left Join(Temp2)

Load *

,If(MaxCounter >= 15, 1,0) as Flag_15DaysCounter Resident Temp3;

Drop Table Temp3;

Temp4:

Load

,If(IsNull(Flag_15DaysCounter),Peek('NewFlag_15DaysCounter'), Flag_15DaysCounter) as NewFlag_15DaysCounter

,If(IsNull(MaxCounter),Peek('NewMaxCounter'), MaxCounter) as NewMaxCounter

,If(IsNull(FromDate),Peek('NewFromDate'),FromDate) as NewFromDate

,If(IsNull(ToDate),Peek('NewToDate'),ToDate) as NewToDate

Resident Temp2 Order By [Employee ID], DATE Desc;

Drop Table Temp2;

Drop Fields FromDate, ToDate, TEMPDATE, MaxCounter, Counter, Flag_15DaysCounter, Flag;

Rename Fields NewFromDate to FromDate, NewToDate to ToDate, NewMaxCounter to Counter, Flag_15DaysCounter to Flag_15DaysCounter;

Left Join (Kronos)

Load * Resident Temp4;

Drop Table Temp4;

EXIT SCRIPT;