Interval Match

detmawin — Fri, 04 Nov 2016 08:59:55 GMT

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

;

topic Interval Match in QlikView

Interval Match