topic Re: Data Modeling Question (optimization) in App Development

Data Modeling Question (optimization)

annick — Sat, 16 Nov 2024 06:44:51 GMT

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

Re: Data Modeling Question (optimization)

mikaelsc — Sun, 27 Jan 2019 14:46:46 GMT

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.

Re: Data Modeling Question (optimization)

annick — Sun, 27 Jan 2019 15:12:18 GMT

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

Re: Data Modeling Question (optimization)

mikaelsc — Sun, 27 Jan 2019 20:07:15 GMT

count distinct users yes (at first sight)

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