5 Replies Latest reply on Jan 16, 2017 1:04 PM by Matthew Risley

    CASE Statement in Tableau

    Balaji Chimakurthy

      Hi team,

       

      I am a new user to Tableau and wanted to bucket the time into different buckets and I am using the below code to do so, but I am facing error doing so. Any help on this will be appreciated. Thanks !!!

       

       

      CASE [Order recd tm]

      when  > "09:00:00" AND < "16:59:59" THEN "9AM - 5 PM"

      when  > "17:00:00" AND < "18:00:00" THEN "5 PM - 6 PM"

      when  > "18:00:01" AND < "18:30:00" THEN "6 PM - 6.30 PM"

      when  > "18:30:01" AND < "19:00:00" THEN "6.30 PM - 7 PM"

      when  > "19:00:00" THEN "After 7PM"

      ELSE NULL

      END

        • 1. Re: CASE Statement in Tableau
          Matthew Risley

          Balaji,

           

          It would help if you could post the error with a screenshot (or better yet the workbook itself).

           

          But my assumption would be that you get the error: "Can't logically 'and' string and boolean values". Is that correct?

           

          To solve this use an IF statement instead. Try:

           

          IF [Order recd tm]  > "09:00:00" AND [Order recd tm] < "16:59:59" THEN "9AM - 5 PM"

          ELSEIF [Order recd tm] > "17:00:00" AND [Order recd tm] < "18:00:00" THEN "5 PM - 6 PM"

          ELSEIF [Order recd tm] > "18:00:01" AND  [Order recd tm] < "18:30:00" THEN "6 PM - 6.30 PM"

          ELSEIF [Order recd tm] > "18:30:01" AND  [Order recd tm] < "19:00:00" THEN "6.30 PM - 7 PM"

          ELSEIF [Order recd tm] > "19:00:00" THEN "After 7PM"

          ELSE NULL

          END

          • 2. Re: CASE Statement in Tableau
            Balaji Chimakurthy

            Hi matthew,

             

            Thanks for your response. I used the below syntax and was able to solve the syntax error in Tableau but I am facing the below error

            [Teradata][ODBC Teradata Driver][Teradata Database] Invalid operation for DateTime or Interval. while I am trying to place the calculated field on to the shelf. Please let me know how to proceed. Thanks in advance!!!!

             

            IF [Order recd tm] > #09:00:00# and [Order recd tm] < #16:59:59# THEN "9AM - 5PM"

            ELSEIF [Order recd tm] > #17:00:00# and [Order recd tm] < #18:00:00# THEN "5PM - 6PM"

            ELSEIF [Order recd tm] > #18:00:00# and [Order recd tm] < #18:30:00# THEN "6PM - 630PM"

            ELSEIF [Order recd tm] > #18:30:00# and [Order recd tm] < #19:00:00# THEN "630 - 7PM"

            ELSEIF [Order recd tm] > #19:00:00# THEN "After 7PM"

            ELSE NULL

            END

            • 3. Re: CASE Statement in Tableau
              Matthew Risley

              Balaji,

               

              These errors are hard to solve without pictures attached or sample workbooks. With those resources it's easier for us to see where the error is coming from

               

              At a glance, it is saying there is a problem with DateTime. Is [Order recd tm] a DATETIME() function?

               

              Also, what happens when you replace "#" with quotes instead?

              • 4. Re: CASE Statement in Tableau
                Balaji Chimakurthy

                Hi matthew,

                 

                I did few modifications to your code as below and now the code ran fine.

                 

                IF DATEPART('hour', [Order recd tm]) >= 9 AND DATEPART('hour', [Order recd tm]) < 17 THEN "9AM - 5PM"

                ELSEIF DATEPART('hour', [Order recd tm]) >= 17 AND DATEPART('hour', [Order recd tm]) < 18 THEN "5PM - 6PM"

                ELSEIF DATEPART('hour', [Order recd tm]) = 18 AND DATEPART('minute', [Order recd tm]) < 30 THEN "6PM - 630PM"

                ELSEIF DATEPART('hour', [Order recd tm]) = 18 AND DATEPART('minute', [Order recd tm]) >= 30 THEN "630 - 7PM"

                ELSEIF DATEPART('hour', [Order recd tm]) >= 19 THEN "After 7PM"

                END

                 

                Thanks for your help.

                • 5. Re: CASE Statement in Tableau
                  Matthew Risley

                  It was my pleasure to help!

                   

                  If I answered your question, or helped, please indicate so! It helps with forum health so we know this can be considered "answered"

                   

                  You can also mark your own response as the answer if you so choose!

                   

                  Best,

                  --Matt