topic Re: How to modelize start-end relationship ? in QlikView

How to modelize start-end relationship ?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to modelize start-end relationship ?

migueldelval — Thu, 10 Nov 2016 11:08:25 GMT

Hi Laura,

Could be possible to attach asample of yor data?

Regards

Miguel del Valle

Re: How to modelize start-end relationship ?

Thu, 10 Nov 2016 11:16:37 GMT

Here are a sample I created.

Re: How to modelize start-end relationship ?

Anonymous — Thu, 10 Nov 2016 11:17:54 GMT

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

Re: How to modelize start-end relationship ?

migueldelval — Thu, 10 Nov 2016 12:36:17 GMT

Hi Laura,

Try it and tell me.

Regards

Miguel del Valle

Re: How to modelize start-end relationship ?

MarcoWedel — Thu, 10 Nov 2016 23:38:10 GMT

use a Crosstable load to get a combined Station field.

regards

Marco

Re: How to modelize start-end relationship ?

MarcoWedel — Thu, 10 Nov 2016 23:58:34 GMT

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