5 Replies Latest reply on Jun 30, 2016 3:40 AM by Robert Senden

    Data 'love triangle'

    Robert Senden

      Hi,

       

      I'm working on a dashboard that shows statistics for airports. The data is not organised by location, the airport, but rather by flights: from the Departure airport to the Arrival airport. However, I want to organise my dahsboard by a single airports (location), regardless whether a flight departs or arrives there.

       

      Example: I want to see statistics on Security queues before boarding, but also on the queues of the Immigration gates after arriving, all at the very same airport, on one dashboard, using the airport manager's name as the filter.

       

      I've got 2 data sources:

      • the statistics
      • the 'Airport Manager contact list'

      Both data sources contain the same list of airport codes (dimension, string), between which I want to establish the relationship. The difficulty is: the statistics distinguish between 'Departure' and 'Arrival' airports, while the contacts list just contains the airport codes.

       

      I want to filter my dashboard on names of the Airport Managers (dimension, string), but I can only establish one relationship between Airport Manager and either the Departure Airport or Arrival Airport (both dimension, string).

      Duplicating could be an option, by duplicating the statistics data source and splitting between Departure Stats and Arrival Stats:

      • establish a relationship between Manager and Dep airport (from Dep Stats data source)
      • also establish one between Manager and  Arrival airport (Arr Stats data source)

       

      However, on doing this, my charts with the duplicate data source have gone blank. Also, my first data source (Dep Stats) kept showing a blue tickmark in the 'Data window', whilst only using data from the duplicate source (Arr Stats) and the airport managers (both orange).

       

      I guess this becomes a complicated 'love triangle', as apparently I can't play cupid between the duplicate data source and the contact list, while having the also a relationship between the original data source and the contact list for the same dimension.

       

      How can I best approach this?

        • 1. Re: Data 'love triangle'
          lei.chen.0

          Hello Robert,

           

          I suggest using custom SQL to process the statistics.

           

          Use something like,

           

          select depart as airport, value1,value2, from statistics

          union all

          select arrive as airport, value1, value2,... from statistics

           

          Then join the result of custom SQL and 'Airport Manager contact list' by airport.

           

           

          Regards.

          • 2. Re: Data 'love triangle'
            Robert Senden

            Hi Lei Chen,

             

            Thanks for shining your light on this. Although I'd love to use SQL to reorganise the data, it is provided by a third party as it is. Perhpas post-processing it by SQL is indeed the only way out, if my local IT department allows me to do that...

            • 3. Re: Data 'love triangle'
              khalid norat

              Could you please share a sample of the data and a sample workbook so it is clearer to see the data and structure.

              How are you linking the 2 data sets

               

              are you using blending or joins

              • 4. Re: Data 'love triangle'
                lei.chen.0

                Hello Robert,

                 

                Hope your IT dept could help you to do that

                 

                By the way, if your data source for statistics is file base, there's another solution by using union.

                 

                For example, assumed it is txt file, say statistics.txt .

                Copy and paste it as statistics2.txt

                Use an editor to open statistics2.txt , and replace field names for depart and arrive.

                Finally, union the two files in data windows

                 

                Regards

                • 5. Re: Data 'love triangle'
                  Robert Senden

                  Thanks for all the help. As I didn't want to wait for IT, if did find a workaround within Tableau.

                   

                  So I have 2 data sources.

                  - Statistics, containing Departure and Arrival Airports

                  - Contacts list, with a list of Airports and their managers.

                   

                  I've simply duplicated the Airports-dimension to create similar 'Departure' and 'Arrival' airports. Both are linked to the Airport manager anyway.

                   

                  By creating seperate worksheets for any type of statistic, I was able to switch links between the Departure Airports and Arrivals airports on and off.
                  In the end I was able to use just one filter on my dashboard for the airport manager to change all the sheets/charts.