1 Reply Latest reply: Jun 14, 2017 1:58 PM by Sebastian Kristof RSS

    How to model multiple associations between tables

    Yves Vandewoude

      Hello,

       

      I would think this question has been asked many times before, but I have looked for advice regarding modelling multiple associations between the same datasets, and haven't really found what I'm looking for.

       

      A really simple example is the following. Assume I have a Person, with two addresses, a home address and a work address, both linking to an address table.

      Person:

      • person_id
      • person_name
      • home_address_id
      • work_address_id

       

      Address:

      • address_id
      • streetname
      • country_id
      • geolocation
      • ...

       

      How do I model this. There are basically two associations between the same tables. Most suggestions I have read somehow summarize as joining the two tables immediately, and copying some data from the address to the person. For example, joining so that you have:

      • person_id
      • person_name
      • home_street
      • work_street
      • ...

       

      That works if address is something I only need the streetname for, but in general that doesn't get me far. I want to follow associations to country and beyond if needed in my dashboards.

       

      A more complex example of fundamentally the same issue is the following.

       

      A project consists of multiple phases.

      Project:

      • project_name
      • project_id
      • formal_kickoff_phase

      Projectphase:

      • project_id
      • phase_name

       

      Here two, there are multiple links: a project has many phases, and there is a specific link to one of those phases. Agreed that in this example, there could also have been a boolean "kickoff" in projectphase, but you don't always control the source datasets.

       

      So my question is twofold:

      1) How do I model multiple associations such as these in qliksense?

      2) Are there any examples on how such multiple relations between two tables can be visualized? For example, how do you indicate which relation to follow when an element is selected?

       

      Thanks in advance!

      Regards,
      Yves

        • Re: How to model multiple associations between tables
          Sebastian Kristof

          I too am having a problem with this. The simplest example is a trip that has an origin and a destination.

           

          Airports:

          • airport_code
          • latitude
          • longitude

           

          Flights_timetable:

          • airport_origin_code
          • airport_destination_code
          • etc...

           

          Qlik Sense only agrees to associate the airport code with one of the fields - either the origin or the destination, but not both. How do I work around this, for example, to build an element that filters my flights on a graph by origin and destination?