First time of day by room

[#1]

Hi,

Lets say i have the following data:

Day	Room	PatientRegistered	AppStart	AppEnd	AppID
8/1/2013	A	07:07	07:15	07:45	1
8/1/2013	A	07:51	07:55	08:10	2
8/1/2013	B	07:15	07:20	07:25	3
8/1/2013	B	07:30	07:35	07:45	4
8/2/2013	A	08:05	08:10	08:40	5
8/2/2013	A	07:30	07:35	07:50	6
8/2/2013	B	08:15	08:30	08:40	7
8/2/2013	B	08:40	08:50	09:10	8

I want to create a line graph that shows by day the first times of the day by room. Purpose is to monitor if appointments and registration started on time.

I did see that it is possible to load the data again grouped by the day and room, but was wondering is it is possible in the Qlikview document also.

Any help would be appreciated.

Robbert

[Anonymous]

Robbert,

Sure you can use "group by" in QV script.  Assuming your data is in table "Table", it would be:

LEFT JOIN (Table) LOAD DISTINCT
Day,
Room,
min(AppStart) as FirstTimeByDayRoom
FROM Table
GROUP BY Day, Room;

Regards,
Michael
PS: As for the line graph - I'm not sure what exactly you want to show.

[#3]

Hi Michael,

What i want to do is create a graph in the Qlikview document and use a function in side the graph to accomplish the samething without loading in script.

Regards

Robbert

[chematos]

You could use an expression with aggr() function in the object of your document.

aggr() is like a group by, this is an example:

aggr(min(Time),Room, Day) where, Room and Day would be the fields of the group by group by.

Hope this helps

[Anonymous · Accepted solution]

The front end analog of the "group by" is the aggr() function, as in:

aggr(min(AppStart), Day, Room)

Could be more helpful if you uploaded the application.

Regards,

Michael

[Anonymous]

hi,

please find the attachment below.

Please let me know if i did any wrong

Thanks

Shaik

[#7]

Hi Michael,

Thx that worked.

Regards

Robbert

[Anonymous]

This is correct.

If fields (Room and Day) are used as dimensions, the expressions are aggregated by these dimensions automatically, and there is no need for any aggr().

Regards,

Michael