4 Replies Latest reply on Nov 25, 2012 10:39 PM by buildstar

    Problem with Case statement

    buildstar

      Hi,

       

      I am trying to create a calculated field using case statement in Tableau that has color coding

       

      CASE

           WHEN cast(cast(DATEPART(hh,getdate()) as varchar)+ ':'+ cast(DATEPART(n,getdate()) as varchar) as datetime) >

                cast(left(Time,2)+ ':' +right(Time,2)as datetime) then 'Processed'

           WHEN cast(cast(DATEPART(hh,getdate()) as varchar)+ ':'+ cast(DATEPART(n,getdate()) as varchar) as datetime) <

                cast(left(Time,2)+ ':' +right(Time,2)as datetime) then 'Yet to Process'

           ELSE 'Not Processed'

      END

       

      But the case statement is not getting created as a calculated field because of syntax error.

       

      Please help me recfity this case statement. "Time" refers to a time column in a table that is part of the dimension in Tableau

       

      thanks

        • 1. Re: Problem with Case statement
          Shawn Wallwork

          Hi Gallop you've got quite a few things going on here. I'm not sure what language you're writing this in, but it's not one Tableau uses. So let's break it down...

           

          The syntax for a CASE statement goes like this:

           

          CASE [My Measure, Dimension or Parameter]

          WHEN 'x' then 'Processed'

          WHEN 'y' then 'Yet to Process'

          ELSE 'Not Processed'

          END

           

          So to start with you need to tell Tableau what to look at and compare values to i.e. [My Measure, Dimension or Parameter]

           

          Next there isn't any 'hh' argument for DATEPART(). Seem as if you're trying to mix in formatting here. Instead you'll want to rewrite it like this:

           

          DATE('hour',[Date])

           

          There also isn't anyway to do the sort of variable substitutions you seem to be trying to do inside DATEPART(), or within a calculated field for that matter. So you'll need to work all those out before getting to the DATEPART() function. Also CAST() is not a function recognized by Tableau. So you'll need to work that out some other way.

           

          Next you seem to be trying to include a Boolean statement inside a CASE() statement, and then put this together as a string (without using the STR() function). Tableau isn't going to let you do this.

           

          Generally speaking you're trying to do far too many things in a single CASE statement without having a firm grasp of Tableau's syntax. Here's the start of the Functions part of the manual. You might want to spend some time looking through it. Also, there's a limited help with functions inside the calculated fields dialog box:

           

          Functions.PNG

           

          Which can give some hints on usage. (Note: clicking that 'help' will take you to the Functions link I posted above.)

           

          Hope this helps, and good luck.

           

          --Shawn

          • 2. Re: Problem with Case statement
            buildstar

            Hi Shawn,

             

            First thanks for your detailed reply. Really appreciate the time you have taken to respond.

             

            The statement runs in SQL Server 2005.

             

            Basically I have  a column that has time in the format Ex: 0300,0315

             

            This time I want to compare with the current system time and if the system value is greater than the field value than the value in the time column needs to be in orange color since it has not processed.

             

            Similarly if the value in the time field is lesser than the current system time then it will be colored red since the file has failed to process.

             

            Also if the time has passed by and the process is complete it will be colored green.

             

            I basically want to color code a bar chart comparing with the system time.

             

            I will go through what you have advised and try to implement.

             

            Also please do let me know if there is any other way to implement such scenario.

             

            Thanks a lot

            • 3. Re: Problem with Case statement
              Shawn Wallwork

              This time I want to compare with the current system time and if the system value is greater than the field value than the value in the time column needs to be in orange color since it has not processed. Similarly if the value in the time field is lesser than the current system time then it will be colored red since the file has failed to process. Also if the time has passed by and the process is complete it will be colored green. I basically want to color code a bar chart comparing with the system time.

               

              So let's put date format aside for the moment, and let's assume that your column of [TIME] is in the same format as Tableau's native format. (Also, I'm a little confused by what's greater than what for which color, but no matter.) Here's the basic structure of your statement:

               

              IF [TIME]< NOW() THEN 'Orange'

              ELSEIF [TIME] > NOW() THEN 'Red'

              ELSEIF [TIME] = NOW() THEN 'Green'

              END

               

              Does this look like it will work for you?

               

              Next we need to get your time format and Tableau's time format to be the same so the equations will evaluate correctly. Would you please post a packaged workbook that includes a bit of your data so I can see the exact format for your TIME column?

               

              Thanks,

               

              --Shawn

               

              EDIT: WOW THAT WAS A MESS! I'VE CORRECTED THE ABOVE. Sorry about that.

              • 4. Re: Problem with Case statement
                buildstar

                Time column is a varchar field with data like

                 

                Ex:

                 

                0400

                0415

                0430

                0445

                0500

                 

                Thanks