Data Modeling Question (optimization)

[annick]

Hello

I have 2 tables: one with bus details (bus number, stations where the bus departs and arrives, associated schedules, number of available seats), one with traveler details (name, the station where the traveller starts his journey, the final station where he arrives).

Here's the load script:

BusTimeTable:
Load * inline
[
BusNumber, FromStation, ToStation, NumberOfSeats, Departure, Arrival, 
201,   A,     B,    12,    10:00,  11:00
201,  B,    C,    10,    12:00,   13:00
201,   C,     D,    8,    14:00,   18:00
202,   A,    D,    20,    09:00,   16:00];

Travellers:
Load * inline
[
Traveller, BusNumber,  StartJourney, EndJourney
Jim,   201,  A,     D
John,  201,  A,    C
Jane,   201,  B,     D
Jack,   201,  C,    D
Sophie,  201,  B,    C
Daniel,     202,  A,    D];

The measures I would like to show are the following:

- Number of travelers departing from a given station even as a transit point e.g. from station B, I expect to see 4 (Jim, John, Jane, Sophie)

- Number of travelers arriving to a given station even as a transit point e.g. to station C, I expect to see 5 (Jim, John, Jane, Jack, Sophie)

- Number of travelers starting their journey from B (not as a transit point) e.g. from B I expect to see 2 (Jane and Sophie)

I don't think that I can achieve this with the current data model. What would be the best one?

Many thanks in advance for your kind help,

kind regards

Annick

[mikaelsc · Accepted solution]

in order to do so, i'd suggest you generate extra data for each journey for each passenger: 

eg from the journey Jim: A-D you'd need to generate 

Journey, User,busnr, Station,Indicator Passage, Indicator StartStation, Indicator EndStation,

JimAD,Jim,201, A,1,1,0

JimAD,Jim,201,B,1,0,0

JimAD,Jim,201,C,1,0,0

JimAD,Jim,201,D,1,0,1

to generate this data, check interval match (qlik help) 

the idea is to match each station occurence in the interval defined by the journey (eg: match C to the interval A-D)

as interval match only works with numerical values, that will be a first challenge. 

after that, 

sum( indicaotr Start station) gives the starting passengers per station,

sum(indicator end) gives the arriving passengers

sum(transit) all the passenger present at a station... 

combining indicators may enable extra measures.

[annick]

Thanks a lot for your quick reply. That answers the questions that I asked. As an additional question, what would be the formula in order to compute the total number of passengers travelling on bus 201? I suppose that I would need to use the distinct or is there another solution? Thanks a lot. Annick

[mikaelsc]

count distinct users yes (at first sight)

this will allow the use of several dimensions (dates, bus lines, stations,...)