topic time scale calculation in QlikView

time scale calculation

Wed, 12 Oct 2011 16:04:44 GMT

I have a table with three colums

colum one is named start time

colum two is named end time

colum three is named employee number

now i want to know how many employees where in the office at different times between 08.00 and 17.00

therefore i use the calculated dimension:

=timestamp(class(frac(Tijd_Start) ,30/(24*60)),'hh:mm')

question is which expresion do I have to use

time scale calculation

Anonymous — Thu, 13 Oct 2011 06:29:44 GMT

Did you try something like:

Count(Distinct EmployeeNumber)

time scale calculation

Thu, 13 Oct 2011 07:39:18 GMT

Thanks for the reply and yes i did ,

but with only the count expression it is calculating the employees available at the start-time and not the time between start and end time.

example: if two employees have a start time at 07.30 and one is left at 09.30 and the other at 12.00

the outcome should be like this:

Starttime: # employees

8h 2

9h 2

10h 1

11h 1

12h 0

time scale calculation

Anonymous — Thu, 13 Oct 2011 08:03:12 GMT

I see your problem.

Is there for you a way to create a flag for every hour that the employee is in?

I think you should do that in your loading script that way you can count those flags as an expression.

time scale calculation

Thu, 13 Oct 2011 08:04:44 GMT

Alias your dimension to the WorkHour. Create the expression like:

Count({<[start time]={"<=WorkHour"},[end time]={">WorkHour+1"}>} Distinct EmployeeNumber)

Regards,

Kiran.

time scale calculation

Thu, 13 Oct 2011 10:50:17 GMT

Yes i think that is a good idea I;ve read a solution for almost the same issue (Hourly Census graph) and

they did something with creating a calander table in combination with intervalmatch# function.

I copied this script but is not working as I have to load the data from a file where the expample script is working with an inline table for the data.

my script now is looking like this but gives an error on the field Date

Directory;

LOAD Patient,

timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,

timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge,

timestamp(floor(Arrive) + Maketime(hour(Arrive)), 'MM/DD/YYYY hh') as ArriveHour ,

Unit

FROM

data.xlsx

(ooxml, embedded labels, table is data);

date_temp:

LOAD

min(floor(Arrive)) as mindate,

max(floor(Discharge)) as maxdate

RESIDENT data

;

LET vMindate = peek('mindate')-1;

LET vMaxdate = peek('maxdate');

DROP TABLE date_temp;

// Generate a calendar table with one row for each hour each date

FOR i = 0 to 23 // Hour 0 to 23

Calendar:

LOAD *,

timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,

$(i) as Hour

;

LOAD

date($(vMindate) + IterNo()) as Date

AUTOGENERATE 1

WHILE $(vMindate) + IterNo() <= $(vMaxdate)

;

NEXT i

// IntervalMatch to connect Patient visits to the Calendar table

IntervalMatch (DateHour)

LOAD ArriveHour, Discharge

RESIDENT data

;

Re: time scale calculation

Anonymous — Thu, 13 Oct 2011 11:49:22 GMT

Your script says:

date_temp:

LOAD

min(floor(Arrive)) as mindate,

max(floor(Discharge)) as maxdate

RESIDENT data

But your loaded table doesn't have the name data.

Try changing the Directory; to data:

time scale calculation

Thu, 13 Oct 2011 12:45:32 GMT

might be usefull but how can i alias my dimension? is this something i have to do in the script?

time scale calculation

Thu, 13 Oct 2011 13:01:41 GMT

no its a pitty but this doesn t work either.

but i agree, it must be something which has to do with the loading of the data

as it works very well for the example with the inline load statement

Re: time scale calculation

Thu, 13 Oct 2011 13:17:49 GMT

In dimensions you can add lable to the calculated dimensions. Ideally it should be done at script level.

Regards,

Kiran.

Re: time scale calculation

Mon, 17 Feb 2014 10:06:49 GMT

Did this get resolved as I'm having a similar problem using the script Marc has posted.

Jim