Find dates

[qvqfqlik]

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'.

[Miguel_Angel_Baeyens]

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.

[qvqfqlik]

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

[qvqfqlik]

stalwar1 can you please help with this

[MK_QSL]

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;

[qvqfqlik]

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

[MK_QSL · Accepted solution]

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,

MK

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;