topic Table in QlikView

Table

Anonymous — Wed, 16 Dec 2015 12:06:06 GMT

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

Re: Table

sunny_talwar — Wed, 16 Dec 2015 14:14:12 GMT

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

];

Re: Table

Wed, 16 Dec 2015 14:46:50 GMT

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:

Re: Table

MarcoWedel — Fri, 18 Dec 2015 00:43:29 GMT

Hi,

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

hope this helps

regards

Marco

Re: Table

MarcoWedel — Fri, 18 Dec 2015 00:50:22 GMT

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