Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
annick
New Contributor II

Data Modeling Question (optimization)

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

 

1 Solution

Accepted Solutions
mikaelsc
Contributor III

Re: Data Modeling Question (optimization)

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.

3 Replies
mikaelsc
Contributor III

Re: Data Modeling Question (optimization)

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
New Contributor II

Re: Data Modeling Question (optimization)

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
Contributor III

Re: Data Modeling Question (optimization)

count distinct users yes (at first sight)

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