Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

juanvg1972
Contributor II

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

Re: Model doesn't link correcty

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

Re: Model doesn't link correcty

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

juanvg1972
Contributor II

Re: Model doesn't link correcty

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);

Partner
Partner

Re: Model doesn't link correcty

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.

juanvg1972
Contributor II

Re: Model doesn't link correcty

Thanks for your help Felip,

Partner
Partner

Re: Model doesn't link correcty

No problem Juan, glad to help .