Table

[Anonymous]

Hi all

I have a table with the following info from a time clock machine

Employee, Date, Time

20022, 2015/12/01, 09:00

20022, 2015/12/01, 13:05

20022, 2015/12/01, 14:30

20022, 2015/12/01, 18:30

20023, 2015/12/01, 09:05

20023, 2015/12/01, 12:55

20023, 2015/12/01, 19:01

20024, 2015/12/01, 18:05

...

I need to show the table as

Employee     Date              Time1     Time2   Time3     Time4

20022             2015/12/01     09:00     13:05     14:30     18:30

20023             2015/12/01     09:05     12:55     19:01

20024             2015/12/01     18:05

The number of times the employee clocks in/out a day can change (as the example)

Any ideas?

Tried pivot table and nothing... cant show the clock in/out in a line

Thank you

Hugo

[sunny_talwar]

Script:

Table:

LOAD *,

  If(Previous(Employee) = Employee, RangeSum(Peek('SN'), 1), 1) as SN;

LOAD * Inline [

Employee, Date, Time

20022, 2015/12/01, 09:00

20022, 2015/12/01, 13:05

20022, 2015/12/01, 14:30

20022, 2015/12/01, 18:30

20023, 2015/12/01, 09:05

20023, 2015/12/01, 12:55

20023, 2015/12/01, 19:01

20024, 2015/12/01, 18:05

];

[#3 · Accepted solution]

Table:

Load

  *

Inline [

Employee, Date, Time

20022, 2015/12/01, 09:00

20022, 2015/12/01, 13:05

20022, 2015/12/01, 14:30

20022, 2015/12/01, 18:30

20023, 2015/12/01, 09:05

20023, 2015/12/01, 12:55

20023, 2015/12/01, 19:01

20024, 2015/12/01, 18:05

];

NewTable:

Load

Employee,

Date,

Time,

rangesum( 1, if(peek('Employee') = Employee and peek('Date') = Date, peek(Clockout))) as Clockout

Resident

  Table

order by Employee, Date, Time asc;

drop table Table;

Then in a pivot table add Employee, Date, and Clockout as Dimensions and only(Time) as expression:

[MarcoWedel]

Hi,

a front end solution (without changing your data model) could be:

hope this helps

regards

Marco

[MarcoWedel]

another option to generate a time sequence field in the script:

table1:
LOAD *,
    AutoNumber(Time,Employee&'|'&Date) as TimeSeqNo
Inline [
    Employee, Date, Time
    20022, 2015/12/01, 09:00
    20022, 2015/12/01, 13:05
    20022, 2015/12/01, 14:30
    20022, 2015/12/01, 18:30
    20023, 2015/12/01, 09:05
    20023, 2015/12/01, 12:55
    20023, 2015/12/01, 19:01
    20024, 2015/12/01, 18:05
];

(given your table is sorted by time, otherwise a resident load might be necessary)

hope this helps

regards

Marco