2 Replies Latest reply on Jul 14, 2016 3:34 PM by amrish lal

    Query too complex error

    Haley Howard

      Hi,

       

       

      I'm a bit new at Tableau and I've been running into this issue for the past few days.

       

       

      I have a few calculated fields that use multiple nested IF statements. I've read through other user's similar issues, and have been working out of an extract, as well as changed one of the calculations to CASE statements instead of IF statements. I know this is definitely the fault of these complex calculated fields, because once I remove them from the workbook there is no longer an error connecting to the data source.

       

       

      Does anyone know what I can to fix this? Ultimately, this workbook will be converted to a live data source and published to a server. I'm only currently working out of an extract that uses the top 20k rows instead of the full amount (approx. 60k). I also can't really publish the data as it contains sensitive information.

       

      Also- one of the fields is a ranking system that could most likely be better written. I know the way I did it works, I'm just not sure if it is the best way. Currently looks similar to this:

       

      IF [Fruit] = "Apples" THEN

          CASE [Season] = "Summer"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "Yes"

                  THEN "10"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "No"

                  THEN "9"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "No" AND [Green] = "Yes"

                  THEN "9"

              WHEN [Granny Smith] = "No" AND [Ripe] = "Yes" AND [Green] = "Yes"

                  THEN "9"

           END

       

      ELSEIF [Fruit] = "Apples" THEN

          CASE [Season] = "Fall"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "Yes"

                  THEN "7"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "No"

                  THEN "6"

              WHEN [Granny Smith] = "Yes" AND [Ripe] = "No" AND [Green] = "Yes"

                  THEN "6"

           END

       

      ELSEIF [Fruit] = "Apples" THEN

          CASE ISNULL([Season])

              WHEN ISNULL([Granny Smith]) AND ISNULL([Ripe]) AND ISNULL([Green])

                  THEN "No data available"

       

           END

       

      ELSEIF [Fruit] = "Oranges" THEN "Ignore"

       

      END

       

       

      However this calculated field has many more lines in it, as the ranking system goes from 10 to 0, and each Case statement uses  YYY, YNY, YYN, NYY, NNY, NYN, YNN, NNN. And [Fruit] is a calculated field as well.

       

       

      Thanks in advance for the help!

        • 1. Re: Query too complex error
          swaroop.gantela

          Haley,

           

          Please see if the attached may be a first step.

          I think the more standard form of the Case Statement may be

          CASE [Season] 

              WHEN  "Summer"     

                    THEN

                          IF [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "Yes"

                          THEN "10"

           

          It may be better as all IFs:

          IF [Fruit] = "Apples" THEN

              IF [Season] = "Summer" THEN

                  IF [Granny Smith] = "Yes" AND [Ripe] = "Yes" AND [Green] = "Yes"

                      THEN "10"

           

          Also if your data source could be changed to all True/False versus Yes/No,

          a simplification could be made:

          IF [Fruit] = "Apples" THEN

              IF [Season] = "Summer" THEN

                  IF [Granny Smith] AND [Ripe] AND [Green]

                      THEN "10"

                  ELSEIF [Granny Smith] AND [Ripe] AND NOT([Green])

                      THEN "9"

           

          There may possibly be some gain in having your rating scale

          in one file and your data in another.

           

          I'll look at it some more, I think further simplifications could be made.

          • 2. Re: Query too complex error
            amrish lal

            Hi Haley,

             

            I am part of Tableau's dev team and would like to take a deeper look at the error message you are getting. I am wondering if you are able to attach a twbx workbook with some dummy data that can used to reproduce the error message?

             

            Thanks,

             

            --Amrish