Calendar or diary view

[#1]

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?

[#2]

Hi,

i hope this is near to your idea.

C u,

Stefano

[#3]

or this also.

Stefano

[#4]

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

or

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!

[#5]

This one?

Stefano.

[#6]

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?

[#7]

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.

[#8]

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!