topic Re: Need help. How to load data in QlikView

Need help. How to load data

Mon, 03 Dec 2012 13:27:50 GMT

Hello together,

i am quite new to QlikView and i have a big problem:

I have this Data:

ID	time	Status
1	06:00	1	<-- check in
2	08:00	1	<-- check in
1	15:00	2	<-- check in to break
1	16:00	3	<-- check out of break
2	18:00	0	<-- check out
1	18:00	0	<-- check out

ID ist the ID of a worker

TIME ist when they check in with different STATUS

1: in

0: out

2: in break

3: out of break

How do i get the working time of each worker?!

How do i get the time for each break?!

I would be so glad if you could help me!

In the Attechment my data.

Of course you dont have to do my work.

But maybe someone could help me out with some tags, so i can find the Formulas I need in the help.

Thank you very much!

Re: Need help. How to load data

MEllinghausen — Mon, 03 Dec 2012 14:26:30 GMT

Hello,

in the first step you must splitt the Field "WHEN" in date and Time in the script.

load [UserID],

left([WHEN],10) as Date_Time,

right([WHEN],5) as Time,

INOUT

from ....times.csv

then you create a table with Dimension ID and formula

sum

({<INOUT={'1'}>} Time) - sum({<INOUT={'0'}>} Time) - (sum({<INOUT={'3'}>} Time) - sum({<INOUT={'2'}>} Time))

and change the numberformat to time

Marcus

Re: Need help. How to load data

Mon, 03 Dec 2012 14:35:42 GMT

Vielen Dank!

Thank you very much.

But I still have one Problem with your solution.

Your Formula only works if the worker takes only one break.

How do I handle more breaks?!

FirstSortedValue?!

thx

Re: Need help. How to load data

MEllinghausen — Mon, 03 Dec 2012 14:47:48 GMT

I think you have a Problem wit the quality of your Data,

e.g. User ID 9 from 14.11.2012 There two states 0

or USERID 6 from 14.11.2012 hase only 0 and so on.

The Formula works.

Marcus