Best practice to list events from different tables

[benjamins]

Let’s take the „Flights“ example, a star schema with the Flights-Table centered and dimension tables “Destination Airport” and “Origin Airport”.

Assuming the fact table contains all flight entries and not – as in the book Qlikview 11 for Developers – the monthly aggregates.

Now I need a report in which all Depatures and Arrivals are listed as chronological events:

Event Time | Event Type | Location

10:00           Arrival          DUS

11:20           Departure     CGN

12:15           Departure     FRA

How could that be achieved in an elegant way (as this is really a small example which I then want to apply on a considerably larger scale)?

Thanks for any help!

[Gysbert_Wassenaar]

You'll have to reorganize your data to put the locations and the times into one field each:

Flights:

Load  

    %Destination as Location

    ArrivalTime as Time

    'Arrival' as ArrivalOrDeparture

From ...source...;

Concatenate(Flights)

Load

    %Origin as Location

    DepartureTime as Time

    'Departure' as ArrivalOrDeparture

From ...source...;

Also, if you only want the names of the destination and arrival airports you can create mapping tables and use applymap instead so you end up with only one table.

[benjamins]

Thank you very much!

However this is what we are  trying to avoid in the first place. The above is only a minimal example. We already have several other charts based on the normalized star (or rather snowflake) model. If we now load a table the way you suggested it we'll have one big blob of data with all the issues arising from that.

Imagine having to analyse not only departure and arrival time, but times for

* Check In (linked to a check in desk)

* Gate In (linked to a gate)

* Gate Out (linked to a gate)

* Boarding Time (linked to a plane and perhaps a gate)

* Time of Baggage claim (linked to belt)

...

and so on. In our case increasing the complexity by regarding trips along several flights for individual passengers (analysing waiting times in between)...