topic Re: Model doesn't link correcty in QlikView

Model doesn't link correcty

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the next model:

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:

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

Can anybody help me??

Thanks in advance

Re: Model doesn't link correcty

felipedl — Thu, 26 Oct 2017 13:21:31 GMT

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.

Re: Model doesn't link correcty

Anonymous — Thu, 26 Oct 2017 13:31:39 GMT

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

Re: Model doesn't link correcty

felipedl — Thu, 26 Oct 2017 13:52:35 GMT

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.

Re: Model doesn't link correcty

Anonymous — Thu, 26 Oct 2017 14:57:32 GMT

Thanks for your help Felip,

Re: Model doesn't link correcty

felipedl — Thu, 26 Oct 2017 15:21:29 GMT

No problem Juan, glad to help .