# Qlik Sense App Development

Highlighted
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).

BusTimeTable:
[
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:
[
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?

kind regards

Annick

Labels (3)

• ### OPTIMIZATION

1 Solution

Accepted Solutions
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,

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
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,

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.

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

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,...)