2 Replies Latest reply on Apr 22, 2016 5:05 AM by Dan Zehner

    Using Multiple Parameters in sequence in custom SQL query

    Dan Zehner

      Hello custom SQL gurus! I'm still pretty new at this part of Tableau, so I'm hoping you can help me out with a real stumper of a problem.

       

      I'm trying to create a custom SQL query for a large Teradata database (a few trillion lines) that dynamically updates what is being queried base on a few parameters: Serial Number Prefix, Start Date, End Date, and Channel ID (this is the one I need help with). Here's the query so far:

      SELECT *

      FROM

      LPSD_BI_SL.LPSD_FACT_1HZ

      WHERE

      SER_NUM IN <Parameters.Serial Number Prefix>

      And GLBL_TM_TS (DATE) BETWEEN <Parameters.Start Date> and <Parameters.End Date>

      And CHAN_ID IN (231,324,325,326,410)

       

      As you can see, I need to input the Channel IDs that I'm interested in as a string separated by commas. I have a separate worksheet connected to a data source that lists the allowable Channel IDs by Serial Number (not all channels are present for each Serial Number). Then the user can select their Serial Number, filter for the Channel IDs they want by Channel Name (so you know what the channel is actually measuring instead of having to know the Channel ID), and ideally it would filter down this parameter I'm trying to create.

      2016-04-21 08-05-51_Start New Discussion _Tableau Support Community.png

      As you can see here, for Serial Number LAJ00572 we only have a few of the channels that I'm interested in. I have the Channel Names filtered down to look for GPS Lat/Long, Ground Speed, Engine Speed, and Intake Manifold Temp, but Engine Speed is not an available channel for this Serial Number. I'm thinking a query like this should work:

      SELECT *

      FROM

      LPSD_BI_SL.LPSD_FACT_1HZ

      WHERE

      SER_NUM IN <Parameters.Serial Number Prefix>

      And GLBL_TM_TS (DATE) BETWEEN <Parameters.Start Date> and <Parameters.End Date>

      And CHAN_NM IN (<Parameters.Latitude Channel>,<Parameters.Longitude Channel>,<Parameters.Data Channel 1>)

       

      Where the Latitude Channel, and Longitude Channel will most likely remain fixed (though there are different versions of  those available, depending on the Serial Number). I think I can then add in a couple of other parameters for Data Channels as needed for my analysis. However, I would like those Data Channels to be filtered by  the same conditions I have on the worksheet. For example, if I wanted to look at the Engine Speed for Serial Number LAJ00572 the parameter shouldn't allow that value since it isn't an available channel for that unit. Any help here would be great, I'm stumped!