topic Re: Calendar or diary view in QlikView

Calendar or diary view

Fri, 23 Sep 2011 10:13:19 GMT

I have the following two tables:

Appointment:
LOAD * INLINE [
    ID, Appointment, Weekday, Time, Transportation
    1, Library, Tuesday, 7:00, Bike
    2, Birthday, Thursday, 9:00, Car
    3, Shopping, Monday, 10:00, Car
    4, Work, Monday, 8:00, Walk
    5, Store, Tuesday, 7:00, Bus
    6, School, Wednesday, 7:00, Bike
];

Weekday:
LOAD * INLINE [
    Weekday no, Weekday
    1, Monday
    2, Tuesday
    3, Wednesday
    4, Thursday
    5, Friday
];

I want to display a table which shows the appointments and the transportation per week day and time.

See the images below for two possiblities:

The difference between the tables is in the red numbered rectangles. Either option would work for me, but I did not get it to work myself.

Does anybody now how to create a table that looks similar to the examples?

Re: Calendar or diary view

Fri, 23 Sep 2011 14:21:55 GMT

Hi,

i hope this is near to your idea.

C u,

Stefano

Re: Calendar or diary view

Fri, 23 Sep 2011 14:35:39 GMT

or this also.

Stefano

Calendar or diary view

Fri, 23 Sep 2011 14:51:57 GMT

Hello Stefano,

Thanks for your reply. The two examples you provide are pretty close, but I would like to see the appointment name and the transportation type in one cell. The examples you provide only shows the appointment name. I would like the cells to display something like this:

Library

Bike

Store

Bus

In your first example you concatenate the appointments names with a comma. Because I want to see the transportation type also, I don't think this one will work for me.

Your second example uses the ID field as an extra dimension. Because of this Monday gets two columns, but these are really not needed, since they fit in one column. If I would have ten appointments on different times on Monday, I would get ten columns. The only time a new column is needed is when the appointment is on the same time and day as another one (see first example image).

I hope you understand what I mean.

Thanks!

Re: Calendar or diary view

Fri, 23 Sep 2011 14:56:47 GMT

This one?

Stefano.

Calendar or diary view

Fri, 23 Sep 2011 15:20:51 GMT

I rewrote it to this:

CONCAT(Appointment & CHR(10) & Transportation & CHR(10) & CHR(10))

That is pretty much what I want to see, but the downside remains that it is all in the same cell. Would you think it is possible to somehow create two cells?

Re: Calendar or diary view

Fri, 23 Sep 2011 15:40:41 GMT

Try this,

but it creates 3 rows on monday.

U can add some '----------------------' to the concat also.

Otherwise i think u have to do something by script.

Stefano.

Re: Calendar or diary view

Mon, 26 Sep 2011 06:33:44 GMT

Thanx Stefano! You pointed me in the right direction. I have altered your last file a bit and got to the result I wanted (see attached qvw).

I added a Group field to the table by using the following code:

Calendar:
LOAD  *,
   IF(Weekday = PREVIOUS(Weekday) AND Time = PREVIOUS(Time), PEEK('Group') + 1, 1) AS Group
RESIDENT Appointment
ORDER BY Weekday, Time;
DROP TABLE Appointment;

In the table I replaced the ID field with this newly added group field.

This does the trick. Pretty simple and obvious actually. Thanks again for you help!