Count If

[#1]

I need to create a stacked bar chart using timestamp data. It is used to show time worked per day by employee. My data (Fact) table has the employee name, punch in (Start) and punch out (End). I need help creating a field that counts the number of times a person punched in on a given day. For example, an employee could have have 3 punch times in one day, that may not necessarily be in chronological order.

How can I assign that row of data a punch count as displayed below for John Doe? The criteria would be based on the Empl name and date.

Empl	Date	Start	End	Type	Punch_Count
John Doe	11/16/14	11/16/14 5:00 AM	11/16/14 7:24 AM	Shift	1
Jane Doe	11/16/14	11/16/14 5:03 AM	11/16/14 11:03 AM	Shift	1
John Doe	11/16/14	11/16/14 8:07 AM	11/16/14 12:55 PM	Shift	3
John Doe	11/16/14	11/16/14 7:24 AM	11/16/14 8:07 AM	Break	2
Jane Doe	11/16/14	11/16/14 11:03 AM	11/16/14 12:15 PM	Break	2

Thanks for all of your help in advance.

[MK_QSL · Accepted solution]

Temp:

Load

  Empl,

  Date(Date#(Date,'MM/DD/YY')) as Date,

  TimeStamp(Timestamp#(Start,'MM/DD/YY h:mm TT')) as Start,

  TimeStamp(Timestamp#(End,'MM/DD/YY h:mm TT')) as End

Inline

[

  Empl, Date, Start, End, Type, Punch_Count

  John Doe, 11/16/14, 11/16/14 5:00 AM, 11/16/14 7:24 AM, Shift, 1

  Jane Doe, 11/16/14, 11/16/14 5:03 AM, 11/16/14 11:03 AM, Shift, 1

  John Doe, 11/16/14, 11/16/14 8:07 AM, 11/16/14 12:55 PM, Shift, 3

  John Doe, 11/16/14, 11/16/14 7:24 AM, 11/16/14 8:07 AM, Break, 2

  Jane Doe, 11/16/14, 11/16/14 11:03 AM, 11/16/14 12:15 PM, Break, 2

];

Final:

Load

  *,

  If(Empl = Previous(Empl) and Date = Previous(Date),RangeSum(Peek('Punch_Count'),1),1) as Punch_Count

Resident Temp

Order By Empl,Date, Start;

Drop Table Temp;

[MarcoWedel]

Left Join (yourtable)

Load *,

         Autonumber(Start, Empl&'/'&Date)

Resident yourtable

Order by Start;

Hope this helps

regards

Marco

[#4]

Thanks a lot!

It appears the previous function slows down the load significantly, but it does exactly what I need it to do so thank you.