Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Model doesn't link correcty

Hi,

I have the next model:

model.png

I want to take this:     count(id_ruta) by pais_aeropuerto.

Dimension = pais_aeropuerto

Expresion = count(id_ruta)

The link is: pais_aeropuerto (airports) -> id_aeropuerto_ori(routes) -> id_ruta(routes)

but it doesn't work:

analisis1.png

when I connect directly:  id_aeropuerto_ori(routes) -> id_ruta(routes). It works:

analisis2.png

Can anybody help me??

Thanks in advance

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Juan,

Since your airports table has both id_aeropuerto_ori and id_aeropuerto_dest, it's creating a synthetic link with the routes table.

Don't know aobut your data, but my guess is that the airports table doesn't have the id_aeropuerto_des and can't create the link in both tables with this synthetic key.


So, when you connect directly to the id_eropuerto_ori works because it has a direct link for only one existing field.

How did you create both (ori and des) fields in the airports table by the way?


Other thing is, why do you have this kind of information on the airports side? To me, this kind of model doesn't make much sense since the airports table is a dimension and your routes table is your fact.


Instead of creating both IDs, use some type of key to remove the synthetic key.

See this community post for some help:


Felipe.

View solution in original post

5 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Juan,

Since your airports table has both id_aeropuerto_ori and id_aeropuerto_dest, it's creating a synthetic link with the routes table.

Don't know aobut your data, but my guess is that the airports table doesn't have the id_aeropuerto_des and can't create the link in both tables with this synthetic key.


So, when you connect directly to the id_eropuerto_ori works because it has a direct link for only one existing field.

How did you create both (ori and des) fields in the airports table by the way?


Other thing is, why do you have this kind of information on the airports side? To me, this kind of model doesn't make much sense since the airports table is a dimension and your routes table is your fact.


Instead of creating both IDs, use some type of key to remove the synthetic key.

See this community post for some help:


Felipe.

Anonymous
Not applicable
Author

Hi Felip,

Yes airports is a dimension table of hc_routes and connect via tu fields;

id_airport_ori and id_airport_des, this fileds are in hc_routes but not

in dm_airports then I had to create it in dm_airports in order to make the links.

You can see in the scripts below

How can I manage this kind of relationship (role-playing dimensions)?

LOAD id_aeropuerto as id_aeropuerto_ori, id_aeropuerto as id_aeropuerto_des, *; /* añadimos ori y des para cruces */

LOAD @1 as id_aeropuerto, @2 as nombre_aeropuerto, @3 as ciudad_aeropuerto, @4 as pais_aeropuerto,

     @5 as cod_iata_aeropuerto, @6 as cod_icao_aeropuerto, @7 as latitud, @8 as longitud, @9 as altitud

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

LOAD @1 as id_aerolinea, @2 as nombre_aerolinea, @3 as alias_aerolinea, @4 as cod_iata_aerolinea,

     @5 as cod_icao_aerolinea, @6 as identificador, @7 as pais_aerolinea, @8 as sw_activa

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

LOAD RowNo() as id_ruta, *; /* anadimos id_ruta */

LOAD @1 as cod_aerolinea, @2 as id_aerolinea, @3 as cod_aeropuerto_ori, @4 as id_aeropuerto_ori,

     @5 as cod_aeropuerto_des, @6 as id_aeropuerto_des, @7 as codeshare, @8 as paradas,

     @9 as equipamientos

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

felipedl
Partner - Specialist III
Partner - Specialist III

Juan,

The way you want it, I can't see a simpler way then having two separate tables, one for origin and destination if you really need the data for the destination airport.

It will basically be a copy of the same table as origin airport, but with the separation of ids for origin and destination.

Other solution would be to create a generic key for each origin / destination aiport combination, but that would create so many new lines in your table, since I believe there would be a lot more combinations than simply duplicating the table.

Attached the code I believe is best.

Anonymous
Not applicable
Author

Thanks for your help Felip,

felipedl
Partner - Specialist III
Partner - Specialist III

No problem Juan, glad to help .