Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have this data from a fingerprint device:
ID | Employee ID | Checktime | Checktype |
---|---|---|---|
1 | 1 | 26/10/17 08:00:00 | In |
2 | 2 | 26/10/17 08:10:00 | In |
3 | 3 | 26/10/17 08:11:00 | In |
4 | 2 | 26/10/1710:10:00 | Out |
5 | 1 | 26/10/17 12:10:00 | Out |
6 | 3 | 26/10/17 15:00:00 | Out |
7 | ... | ... | ... |
I want to know the hours worked for every employee in every day. For example I want to get:
Employee ID | Date | Hours worked |
---|---|---|
1 | 26/10/17 | 6 |
2 | 26/10/17 | 8 |
3 | ... | ... |
So I need to get from every "In" row the next "Out" row for the same Employee ID and get the difference in hours between two dates.
Any suggestion? Thanks!
I am not sure I understand.. can you share a sample to check this out?
I get error loading script: "Field CheckTime not found"
In your first response you say that I have to put two dimensions: EmployeeID and Date.
In what way can I get the "Date" dimension? Only I have timestamps.
You can create a date field in the script like this
Date(Floor(checktime)) as Date
Take out the drop table.
It shouldn't happen, but sounds like the two tables are auto concatenating.
Thanks,
Smon
I had to do something similar in one of my projects for a help ticket tracker.
In your scenario, you could just replace the 'created' field value with your 'Clock-in' value and the 'resolved' field value with your 'clock-out' value. I would imagine similar results.
Resolution Time expression is:
Interval(interval#(ceil(($(vResolutionTime))),'s'), 'dd:hh:mm:ss')
where the variable is defined as:
(resolved - created) *86400