pivot table where student name should appear on cells based on 2 dates

[oliviatww]

Hi all,

Can someone please help?

BEFORE (table1):

Room - a field from database table

Start Date - a field from database table

End Date - a calculated field based on the calculation

AFTER (table2): (what I want to be in QV)

Date - 10 days starting from today's date (assuming today's date is 28/06/2016)

So if you think this is a course where students are arranged in what room from what date (start) to what date (end):

Different examples:

1. Alan in Room1, start date of the course is 29/06/2016 and end date of the course is 01/07/2016. So in table 2, Alan should be placed under those days which is 3 days between 29/06/2016 and 01/07/2016 under Room1
2. Adam in Room3 who started before today's date
3. Richard, Wesley and Russell who are arranged in the same room on the same day(s)

Any helps would be much appreciated!!!

Thanks,

Olivia

[marcus_sommer]

I think you need to match your start- and end-date with a master-calendar per IntervalMatch and then you could use room and date as dimensions with an expression like: concat(People, chr(10)).

- Marcus