Peek is slow. Any workaround?

[madnanansari]

I am working on Employee Attendance data.  The attendance data has Employee Id, Attendance date, Date & Time In and Date & Time Out.

My requirement is to make a table where I list all the hours when an Employee is present. That means if an employee punches in at 8Am and Punches out at 5PM; I need the below hours in my employee attendance hours table:

8

9

10

11

12

13

14

15

16

17

When I write the code; I use the Peek function in a do while loop at it takes more than an hour to parse 100 thousand records. Is there any workaround to speed up the code?

[madnanansari]

The below is the code:

LET vNumRows = NoOfRows('Attendance');rdNo=1;

[Employee Attendance Hour Track]:
NoConcatenate
Load 0 as [Date1], 0 as [EmpId], 0 as [Hour1], 0 as i, 0 as MyRecordNo AutoGenerate 0;

FOR i=0 to $(vNumRows)-1

LET vAttendanceDate=Date(Peek('Attendance Date',$(i), 'Attendance'),'D/M/YYYY');
LET vEmployeeId=Peek('Employee Id',$(i), 'Attendance');

LET vStart=Peek('Period Start',$(i), 'Attendance');
LET vEnd=Peek('Period End',$(i), 'Attendance');
//do some stuff....

Do while vStart <= vEnd
Let vHour=hour(vStart);

Concatenate [Employee Attendance Hour Track]:
Load '$(vAttendanceDate)' as [Date1], '$(vEmployeeId)' as [EmpId], $(vHour) as [Hour1], $(i) as i, $(vMyRecordNo) as MyRecordNo AutoGenerate (1);

Let vStart = Timestamp(vStart + 1/24) ;
Let vMyRecordNo = vMyRecordNo + 1;

Loop

LET vAttendanceDate=Null();
LET vEmployeeId=Null();

LET vStart=Null();
LET vEnd=Null();

Next

[Anil_Babu_Samineni]

We may help to optimize your script if you can help us your script?

[Anil_Babu_Samineni]

For me script is good as per flow and process after my debug. Can we know where you feel the performance is bad? Is that loading long time (If so, confirm how many records you are pulling)

[madnanansari]

it processes 60k records in 4 min and then with every record becoming slower...

[Fernando_Fabregas]

Hi! A way to reduce many loops is to replace entire Do While..Loop section with something like this: 

Concatenate [Employee Attendance Hour Track]:
Load '$(vAttendanceDate)' as [Date1], '$(vEmployeeId)' as [EmpId], Hour as [Hour1], $(i) as i, $(vMyRecordNo) as MyRecordNo

RESIDENT Hours WHERE Hour >= $(vStart) AND Hour<=$(vEnd);

You must create a table Hours with a field Hour from 0 to 23.

Note that only works fine if end hour is within the same day (<24).

I think, in 8 hours working days, you can reduce loops up to 88%...

Regards, Fernando

[Kushal_Chawda]

@madnanansari  may be try below. make sure that all your Dates or timestamp fields are in proper format.

Data:
LOAD Emp_ID,
     Attendence_Date,
     hour(Period_Start)+iterno()-1 as Hours
     Period_Start,
     Period_End
FROM Table
while hour(Period_Start)+iterno()-1<= hour(Period_End);

[Lucian]

I would use IntervalMatch() with that Hours table. No loops.

[ArnadoSandoval]

Hi @madnanansari 

I implemented @Kushal_Chawda solution into the attached solution; I added an additional presentation of the data, as you will find out if you look at the application!

hth

[madnanansari]

Hello Fernando, this will not work. This is 24x7 operation so night shift starts at 7 and end at 7 morning at some places.