Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
steeramp
Partner - Creator II
Partner - Creator II

Do While Loop : number days absence Employee

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 !!

1 Solution

Accepted Solutions
steeramp
Partner - Creator II
Partner - Creator II
Author

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

View solution in original post

8 Replies
ogautier62
Specialist II
Specialist II

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Specialist
Partner - Specialist

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
Partner - Creator II
Partner - Creator II
Author

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

ogautier62
Specialist II
Specialist II

ok

one condition :

rows must be sorted on %key_emp date_abs

ogautier62
Specialist II
Specialist II

Hi

days must be consecutive

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

steeramp
Partner - Creator II
Partner - Creator II
Author

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
Specialist II
Specialist II

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