4 Replies Latest reply on Nov 21, 2013 3:34 AM by Russell Christopher

    PASS THROUGH SQL TO GENERATE A NEW COLUMN

    Andres Ustariz

      Hi Everyone,

       

      I need to split my list of values in two. There is no dimension or Measure I can use to define each group other than telling Tableau which particular members to group. I tried a group but it won't help for what I need since it won't allow me to aggregate some members from one group along with some members from the other.

      So I think the best option is to pass a  CASE (ORA) SQL sentence to create a column which will allow me to select member regardless of their group but also quickly filter by group if needed.

      This is what I wrote in ORACLE and is working, but I haven't been able to use it properly in Tableau.

      ORACLE SQL

      SELECT 

      (CASE

      WHEN DIM_ID = '103606309' THEN 'Y'

      WHEN DIM_ID = '102406309' THEN 'Y'

      WHEN DIM_ID = '122706309' THEN 'Y'

      WHEN DIM_ID = '022506309' THEN 'Y'

      WHEN DIM_ID = '082706309' THEN 'Y'

      ELSE 'N'

      END) CHECK

      FROM SERVER.TABLE

       

      TABLEAU SQL SENTENCE

      RAWSQL_STR("SELECT

      (CASE

      WHEN DIM_ID = '103606309' THEN 'Y'

      WHEN DIM_ID = '102406309' THEN 'Y'

      WHEN DIM_ID = '122706309' THEN 'Y'

      WHEN DIM_ID = '022506309' THEN 'Y'

      WHEN DIM_ID = '082706309' THEN 'Y'

      ELSE 'N' END)")

       

      With the RAWSQL calculation I keep getting this error even if I include the FROM portion:

      error.png

      Because of the amount of data I wouldn't like to go with custom SQL.

       

      I can't alter the original source for it is going to be a temporary comparison.

       

      I appreciate your help solving this issue.

       

      Andres

        • 1. Re: PASS THROUGH SQL TO GENERATE A NEW COLUMN
          Russell Christopher

          Hi!

           

          You should be able to create a Tableau expression which does this nicely.

           

          Here's an abbreviated calculated field, which you might call "Check":

           

          CASE [DIM_ID]

               WHEN  '103606309' THEN 'Y'

               WHEN  '102406309' THEN 'Y'

               ...

               ELSE 'N'

          END

           

          There doesn't seem to be a need for you to use Custom SQL or RAWSQL unless I'm missing something.

           

          Let us know?

          1 of 1 people found this helpful
          • 2. Re: PASS THROUGH SQL TO GENERATE A NEW COLUMN
            Bora Beran

            As Russel said you should be able to do this in a calculated field in Tableau as well but as a more general comment about RAWSQL you can fix the error by removing SELECT.

             

            RAWSQL_STR("(CASE

            WHEN DIM_ID = '103606309' THEN 'Y'

            WHEN DIM_ID = '102406309' THEN 'Y'

            WHEN DIM_ID = '122706309' THEN 'Y'

            WHEN DIM_ID = '022506309' THEN 'Y'

            WHEN DIM_ID = '082706309' THEN 'Y'

            ELSE 'N' END)")

             

            Assume your query is

             

            SELECT A, B, C from TableX

             

            What Tableau will do with RAWSQL is to add it like another column to your query so it becomes (in abbreviated form)

             

            SELECT A, B, C, (CASE WHEN DIM_ID =.......END) as [NameGeneratedByTableauFortheCalculation] from TableX

             

            As a result, when you add something like SELECT it breaks the query.

            1 of 1 people found this helpful
            • 3. Re: PASS THROUGH SQL TO GENERATE A NEW COLUMN
              Andres Ustariz

              Thanks Russell and Bora for your contributions.

               

              I got the following:

               

              Using the Parameter approach I cannot select random DIM_ID's and get the accurate value 'Y' or 'N' value based on the calculation. Once one of the values is present in the filtered list, all the CHECKS turn to 'Y' even if they don't belong to the grouping list.

               

              Using the RAWSQL approach ( BTW Thanks Bora for the straightforward explanation) I obtained the expected result. Nonetheless my interest is to be able to plot both groups independently (if needed) i.e: just by having the calculated field in the level of detail. I got the following error when I tried this:

              error1.png

               

              Let me know if I'm missing something.

              1 of 1 people found this helpful
              • 4. Re: PASS THROUGH SQL TO GENERATE A NEW COLUMN
                Russell Christopher

                Sorry, I don’t quite follow. Any chance you could throw a couple rows into Excel and use that as your data source – post your workbook based against Excel (as a Packaged workbook) and we can see exactly what you mean.