Nested loop with compansion

[katalinali]

I would like to generate a monthly staff attendance report. I have two types of record, (1) monthly annual leave record:
Staff name, 1_Sep, 2_Sep, 3_Sep....
Mary, NA,NA,NA...
John,AL, NA, AL....
(2) daily in time record (e.g for 1 September, suppose the check in time should be before 9:00)
Staff name, in-time
Mary, 9:01
John, NA
Is it possible to make nest loop and generate a monthly report, so that i can determine if the staff is late or not.
The result table:
Staff name, 1_Sep, 2_Sep.....
Mary, YES, NO...
John, NO,NO

[sundareshr]

Here's one approach you could try.

Create a lookup of the annual leave record (say annual_leave.csv). This csv should have 3 columns Staff Day OnLeave (change the format of your current list). Preferably, this should only contain Day when Staff is on leave. (John 1_Sep AL)

Then, run this query for your final report

... | eval dm=strftime(_time, "%-d_%b") | lookup annual_leave.csv Staff AS "Staff name" Day AS dm OUTPUT OnLeave | eval intime=strptime(x, "%-H:%M") | eval starttime=strptime("9:00", "%-H:%M")| eval ontime=case(OnLeave="AL", "N/A", isnull(OnLeave) AND intime>starttime, "No", intime<starttime, "Yes", 1=1, "UNK") | chart values(ontime) as ontime over "Staff name" by dm

Assumptions:
_time should be time from daily in-time record, representing each day of the month
dm format should match format of values in Day field in leave csv.
OnLeave column only has "AL" representing "Leave"