1 Reply Latest reply on Nov 13, 2018 4:33 PM by patrick.byrne.0

    SQL/Tableau Query for Interactive Dashboard

    Marcus Bright

      Hi Forum,

       

      Hoping you can help me with a request I have had come through for a Tableau viz. The basis of it is that I’ve been given 3 different tables and a mock-up chart of how the output should look.

       

      Reference Tables:

       

      ID            Regional Code

      1              KF1

      1              KF2

      2              KF3

      3              KF4

                     

      ID            County

      1              Dorset

      2              Somerset

      3              Gloucestershire

                     

      Main Table –

                     

      Category              Regional Code   Price

      Category1           KF1                         -

      Category2           KF1                         -

      Category3           KF2                         -

      Category4           KF2                         -

      Category5           KF2                         -

      Category6           KF3                         -

      Category7           KF4                         -

       

      What they want is to be able to have a bar chart in Tableau that will show a specific category from the Main Table as well as a specific county which is then then split by the cost of activity both ‘Inside’ and ‘Outside’ a county…with options to then allow end-users to be able to switch the category and county.

       

      I’m not entirely sure what the best way to approach this is and so far my queries have not worked, to get it at least working I’ve thought about creating a column via SQL for each county based on the 2nd reference table, i.e.

      ‘CASE WHEN Regional Code in ('KF1',’KF2') THEN 'Within County'

      ELSE 'Outside Count'

      END AS 'Dorset’

       

      This is very inefficient however, and I also have the issue that the Regional Codes can and will change which county they come under so it needs to be dynamic. 

       

      Any ideas?

       

      Kind Regards,

       

      Marcus

        • 1. Re: SQL/Tableau Query for Interactive Dashboard
          patrick.byrne.0

          Hello Marcus,

           

          Another option would be to creat the new dimension based on a calcuated field. Similar to the SQL query that you outlined above, but there might be similar limitations to the ones experienced with the query, i.e. not truly dynamic.

           

          Qucik question, does any relationship exist in the data tables that could be used to distquish when they are in or out of the county? If we could find a relationship in the data, these could be used to build the calculations for creating the in county/out of county.

           

          Another option would be to use sets, creating an "in-county" set and "out-of-county" set that could be used for filtering in the workbook.

           

          I would also love to see a workbook attached! It helps our Community users assist quickly and easily.

           

          Packaged workbooks: when, why, how

           

          Hope this helps!

           

          Cheers,

          Byrne, Patrick