Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
detmawin
Contributor III
Contributor III

Interval Match

I am building a data model of employee shifts, so at any hour the application will reveal what department they are in.  I have a single column of clock in times, and another column that gives the legend for the type of clock in, in, out, and startshift.

This is what I've done so far, I broke out the times to determine which is a start time and end time.  The idea was to use interval match to find out how many were on staff at any given hour.  I build out the calendar and the date hour field does not work.  When I broke out my start shift and stop shift from the singe column, when I loaded the data into a table box, either the start time or stop time is null.  So if an employee clock in once, it will show like this:

The employee shift times is in one column like this.

1/01/20116 8:00 am

01/01/2016 5:00 pm

Thank you Kindly for your help.

start time    end time

9 am

                    5pm.

Here is the script:

Shift:

Load Distinct

  *,

  IF(Match(Shift_Flg, 'ClockIn'),date(EmployeePunchDateTime,'MM/DD/YYYY hh:mm' )) AS [ShiftStart],

  IF(Match(Shift_Flg, 'ClockOut'),date(EmployeePunchDateTime, 'MM/DD/YYYY hh:mm' )) AS [ShiftEnd]

;

Load

  EmployeeNumber,

  PUNCHDTM as EmployeePunchDateTime,

     date(floor(PUNCHDTM)) AS EmployeePunchDate,

     timestamp(floor(PUNCHDTM) + Maketime(hour(PUNCHDTM)), 'MM/DD/YYYY hh') as ShiftHour,

     Time(PUNCHDTM) as EmployeePunchTime,

    if(PUNCHOVERRIDE='New Shift' or PUNCHOVERRIDE='NULL' or PUNCHOVERRIDE='In Punch', dual('ClockIn',1),dual('ClockOut',0)) as Shift_Flg,

    PUNCHOVERRIDE as EmployeePunchStatus

Resident Employee;

date_temp:

LOAD

  min(floor(ShiftStart)) as mindate,

  max(floor(ShiftEnd)) as maxdate

RESIDENT Shift;

;

LET vMindate = peek('mindate')-1;

LET vMaxdate = peek('maxdate');

DROP TABLE date_temp;

FOR i = 0 to 23 // Hour 0 to 23

  Calendar:

  LOAD *,

  timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,

  $(i) as Hour

  ;

  LOAD

  date($(vMindate) + IterNo()) as Date

  AUTOGENERATE 1

  WHILE $(vMindate) + IterNo() <= $(vMaxdate)

  ;

NEXT i

left join(Shift)

IntervalMatch (DateHour)

LOAD ShiftStart, ShiftEnd

RESIDENT Shift

;

0 Replies