Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Left Join (yourtable)
Load *,
Autonumber(Start, Empl&'/'&Date)
Resident yourtable
Order by Start;
Hope this helps
regards
Marco
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.