Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

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

Re: Do While Loop : number days absence Employee

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
Valued Contributor II

Re: Do While Loop : number days absence Employee

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

Partner
Partner

Re: Do While Loop : number days absence Employee

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

Partner
Partner

Re: Do While Loop : number days absence Employee

Thats the wrong use of peek.

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

Partner
Partner

Re: Do While Loop : number days absence Employee

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

ogautier62
Valued Contributor II

Re: Do While Loop : number days absence Employee

ok

one condition :

rows must be sorted on %key_emp date_abs

ogautier62
Valued Contributor II

Re: Do While Loop : number days absence Employee

Hi

days must be consecutive

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

Partner
Partner

Re: Do While Loop : number days absence Employee

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
Valued Contributor II

Re: Do While Loop : number days absence Employee

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