Do While Loop : number days absence Employee

[steeramp]

Hi to all,

I'm not idea hot to resolve this problem:

in my Table Tab_Absence I have 2 column : KEY_EMPLOYE and Date_Absence;  (.QVD)

I want to know how many consecutive working days the employee X has made to absence.

Example:

%Key_Employe: 403

Date_ Absence :  20-03-2018

Nr_Days : 1

%Key_Employe: 403

Date_ Absence :  30-03-2018

Nr_Days : 1

%Key_Employe: 403

Date_ Absence :  03-04-2018

Nr_Days : 5 ( because He's absent also , 04-04-2018 ,  05-04-2018 ,  06-04-2018 ,  09-04-2018 )

%Key_Employe: 403

Date_ Absence :  24-07-2017

Nr_Days : 15 ( because He's absent from 24-07-2017 to 14-08-2017 , ** attention 01-08-2018 is National Day)

Thanks in advance all !!

[ogautier62]

Hi,

you can use function peek() to compare current record with prior :

load *, if(peek('key_emp') = key_emp and peek('date_abs') = date_abs-1, peek('nr_days') +1,1) as nr_days

resident absence;

pb is with non working days,

for that add a column prior_day_worked in date_abs from your calendar

regards

[steeramp · Accepted solution]

Hi Gautier,

thanks fro your answer...  but seems not correct... !

I have add your code :

Absence:

Load

*,

if(peek('%Key_employe') = %Key_employe and peek('Date_Absence') = Date_Absence-1, peek('nr_days') +1,1) as nr_days

Resident Tab_Absence;

but the value for the column Nr_Days is always 1

[bramkn]

Thats the wrong use of peek.

what you can do is loop through employees and load their absence records and count the nr_days

[steeramp]

Yes... this was my first idea.... but ... how can do this ?

[ogautier62]

ok

one condition :

rows must be sorted on %key_emp date_abs

[ogautier62]

Hi

days must be consecutive

it's not total day absence but consecutive days, so use of peek

[steeramp]

Yeahh...  I was using Qualify *

probably for that didn't works....  Now with this code , the count it's correct:

Tab_Absence:

LOAD

    %Key_employe,

    DateBloc as Date_Absence   

Resident BlocTempDuree

Where isFlagBloc_Absence=1

Order by %Key_employe ,DateBloc;

Absence:

Load

*,

if(%Key_employe=peek(%Key_employe) and peek(Date_Absence) = Date_Absence-1 , peek(nr_days) +1,1) as nr_days

Resident Tab_Absence;

Drop Table Tab_Absence;

I need to fix the working days

Thank you for your effort

[ogautier62]

another thing :

for non worked day you have to calculate in master calendar prior_work_day then load it in emp_abs and compare peek('date_abs') with prior_day_work because not necessary day + 1 !

good dev