topic Re: Peek is slow. Any workaround? in App Development

Peek is slow. Any workaround?

madnanansari — Sat, 16 Nov 2024 17:38:34 GMT

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:

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?

Re: Peek is slow. Any workaround?

madnanansari — Wed, 06 Jan 2021 11:26:23 GMT

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();

Re: Peek is slow. Any workaround?

Anil_Babu_Samineni — Wed, 06 Jan 2021 11:26:55 GMT

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

Re: Peek is slow. Any workaround?

Anil_Babu_Samineni — Wed, 06 Jan 2021 11:33:15 GMT

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)

Re: Peek is slow. Any workaround?

madnanansari — Wed, 06 Jan 2021 11:38:43 GMT

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

Re: Peek is slow. Any workaround?

Fernando_Fabregas — Wed, 06 Jan 2021 13:10:53 GMT

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

Re: Peek is slow. Any workaround?

Kushal_Chawda — Wed, 06 Jan 2021 22:36:06 GMT

@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);

Re: Peek is slow. Any workaround?

Lucian — Wed, 06 Jan 2021 23:31:59 GMT

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

Re: Peek is slow. Any workaround?

ArnadoSandoval — Thu, 07 Jan 2021 02:59:43 GMT

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

Re: Peek is slow. Any workaround?

madnanansari — Wed, 13 Jan 2021 07:58:43 GMT

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

Re: Peek is slow. Any workaround?

madnanansari — Thu, 14 Jan 2021 12:17:32 GMT

Hello Arnado,

I am working on a supermarket data which works 24x7 so Some people shift starts at 7Am and some in the evening at 7PM. If you can change the code accordingly?

Re: Peek is slow. Any workaround?

ArnadoSandoval — Fri, 15 Jan 2021 04:02:47 GMT

Hi @madnanansari

You should describe your problem trully, without leaving any details out; I don't understand why you did noy try to extended the application I attached (Z_Attendance_01.qvf) one of the Qlik Community functions is to guide developers like you to understand and learn the product; this is not a "Hire a Qlik Developer" Community, if that is what you are looking for, post your question in those sites the offer "Developers for Hire".

Now going back to your question, as I already upload the application: Z_Attendance_01.qvf Would you please ask a question based on where in its script you are experiencing difficulties? or clearly ask for the application to be fully written for you?

hth

Re: Peek is slow. Any workaround?

thi_pham — Fri, 15 Jan 2021 06:09:11 GMT

I think you could make a join between 2 tables:

Temp:

Load Date, Hour resident date_hour;

output join

Employee Id, Attendance date, Date , TimeIn,TimeOut;

Data:

Noconcatenate

Load *,

if (hour >=TimeIn and hour <=TimeOut, 1, 0 ) as IsBuildHour;

resident Temp;

Use column IsBuildHour = 1 to get your result.