How to modelize start-end relationship ?

[#1]

Hey everyone,

I am creating an application about coaches and journeys. In my main table, I have the station from where the coach leaves and the station where the coach arrives.

Like this, and with other informations about the journey (number of passengers ...)

JourneyID                Start_StationID                   End_StationID

xxxx                           Houston56                        Detroit 26

And I have another table with information about the Stations (adress ...).

I want to display in my application a graph about all the journey thats leaves from stations and another about all the journeys that arrives in that station.  And I want to be able to select a station in a filter and then, see for this station, informations about the journeys leaving from there and arriving...

But I don't know how to do it !  At first I wanted to join  Start_StationId with a  "Start_Stations" table and create another similar  "End_Stations" table but I wouldn't be able to select one "station" field to get all my information...

Any idea ?

Please tell me if I'm not clear

Thank you for your help

[migueldelval]

Hi Laura,

Could be possible to attach asample of yor data?

Regards

Miguel del Valle

[#3]

Here are a sample I created.

[Anonymous]

Hi Laura,

• You should create a dimension table containing all Stations.

Use this script, only if you do not have this table

DIM_STATION;

LOAD Distinct

     Start_StationID As StationID,

     Start_StationID As Station

From YOUR_TABLE;

Concatenate ('DIM_STATION')

LOAD Distinct

     End_StationID As StationID,

     End_StationID As Station

From YOUR_TABLE;

• Then create a mapping table do as follow:

MAPPING_STATION_JOURNEY:

LOAD

     Start_StationID As StationID,

     'Start' As Data,

     JourneyID

From YOUR_TABLE;

Concatenate ('MAPPING_STATION_JOURNEY')

LOAD

     End_StationID As StationID,

     'End' As Data,

     JourneyID

From YOUR_TABLE;

• When creating your KPI, use the field "Data" in the set analysis to distinguish start from end.

You can still have two dimensions for Start and End Stations, to use relashonship (ie need to known jorney from a station to another one)

Hope to help you,

Vincent

[migueldelval]

Hi Laura,

Try it and tell me.

Regards

Miguel del Valle

[MarcoWedel]

use a Crosstable load to get a combined Station field.

regards

Marco

[MarcoWedel]

maybe like this:

tabMain:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1157573-253168/20161110_sampledata.xlsx] (ooxml, embedded labels, table is Main);

tabStations:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1157573-253168/20161110_sampledata.xlsx] (ooxml, embedded labels, table is Stations);

tabLink:
CrossTable (StationIDField, Stations_ID)
LOAD JourneyID,
    Start_StationID,
    End_StationsID
Resident tabMain;

Left Join
LOAD Distinct
    StationIDField,
    SubField(StationIDField,'ID',1) as StationType
Resident tabLink;

hope this helps

regards

Marco