Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
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
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
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
Thats the wrong use of peek.
what you can do is loop through employees and load their absence records and count the nr_days
Yes... this was my first idea.... but ... how can do this ?
ok
one condition :
rows must be sorted on %key_emp date_abs
Hi
days must be consecutive
it's not total day absence but consecutive days, so use of peek
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
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