6 Replies Latest reply on Dec 14, 2017 10:45 AM by John Sobczak

    Expect then to match if at character 0

    Cheryl Morgan
    Cheryl Morgan Dec 14, 2017 9:35 AM

      I have read several solutions to see if they would help my cause and they have not. So can anyone enlighten me as to why i am getting the error below. I have used #, ' and this " as characters based on what i have read, so i am at a complete loss. My prior issue with my times was addressed and fix. thank you for help with that issue.

      Thank  you in advance. 

       

      IF  [Collect_Time2]>= 00:00:00 AND  [Collect_Time2] <= 02:59:00 THEN "0000-0259"

      ELSEIF  [Collect_Time2] >= 03:00:00 AND [Collect_Time2] <= 05:59:00 THEN "0300-0559"

      ELSEIF  [Collect_Time2] >= 06:00:00 AND [Collect_Time2] <= 08:59:00 THEN "0600-0859"

      ELSEIF  [Collect_Time2] >= 09:00:00 AND [Collect_Time2] <= 11:59:00 THEN "0900-1159"

      ELSEIF  [Collect_Time2] >= 12:00:00 AND [Collect_Time2] <= 14:59:00 THEN "1200-1459"

      ELSEIF  [Collect_Time2] >= 15:00:00 AND [Collect_Time2] <= 17:59:00 THEN "1500-1759"

      ELSEIF  [Collect_Time2] >= 18:00:00 AND [Collect_Time2] <= 20:59:00 THEN "1800-2159"

      ELSEIF  [Collect_Time2] >= 21:00:00 AND [Collect_Time2] <= 23:59:00 THEN "2200-2359"

      ELSE ' '

      END

      • 342 Views
      • Categories: Dates & Times, Formatting & Display, Queries & Performance
      • Tags:
        • 1. Re: Expect then to match if at character 0
          John Sobczak
          John Sobczak Dec 14, 2017 9:49 AM (in response to Cheryl Morgan)

          The issue is with the values you are using eg. 12:00:00   you will need to put this in a actual date & time format or decimal format.

          1 of 1 people found this helpful
          • 2. Re: Expect then to match if at character 0
            Cheryl Morgan
            Cheryl Morgan Dec 14, 2017 10:07 AM (in response to John Sobczak)

            Since i am new to Tableau, i am not sure i know what you saying, the data from the database is already formatted as a time field. or should i say as a date time field, but there is no date with the time. Sorry to bother you again.

            1 of 1 people found this helpful
            • 3. Re: Expect then to match if at character 0
              John Sobczak
              John Sobczak Dec 14, 2017 10:10 AM (in response to Cheryl Morgan)

              Cheryl,  No bother at all.  I don't think Tableau recognizes a time field by itself.   If you go to the change field properties, there is no option for only a time field, only date or date & time.

              1 of 1 people found this helpful
              • 4. Re: Expect then to match if at character 0
                arvindgarg
                arvindgarg Dec 14, 2017 10:39 AM (in response to Cheryl Morgan)

                I rewrote your statement like below, I think, it should work

                 

                 

                IF  DATEPART('hour', [Collect_Time2])>= 0 AND  DATEPART('hour', [Collect_Time2])  <= 2 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "0000-0259"

                 

                 

                ELSEIF DATEPART('hour', [Collect_Time2])>= 3 AND  DATEPART('hour', [Collect_Time2])   <= 5 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "0300-0559"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 6 AND  DATEPART('hour', [Collect_Time2])   <= 8 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "0600-0859"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 9 AND  DATEPART('hour', [Collect_Time2])   <= 11 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "0900-1159"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 12 AND  DATEPART('hour', [Collect_Time2])   <= 14 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "1200-1459"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 15  AND  DATEPART('hour', [Collect_Time2])   <= 17 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59) THEN "1500-1759"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 18 AND  DATEPART('hour', [Collect_Time2])   <= 20 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59)  THEN "1800-2159"

                 

                 

                ELSEIF  DATEPART('hour', [Collect_Time2])>= 22  AND  DATEPART('hour', [Collect_Time2])   <= 23 and

                (DATEPART('minute', [Collect_Time2]) >=0 AND DATEPART('minute', [Collect_Time2]) <= 59)  THEN "2200-2359"

                ELSE ' '

                END

                • 5. Re: Expect then to match if at character 0
                  Cheryl Morgan
                  Cheryl Morgan Dec 14, 2017 10:44 AM (in response to arvindgarg)

                  Beautiful, thank you so much!! this worked. Now i see how to format such a statement.

                  • 6. Re: Expect then to match if at character 0
                    John Sobczak
                    John Sobczak Dec 14, 2017 10:45 AM (in response to arvindgarg)

                    I also just discovered if you wrap your time in #'s it should work (eg.  #10:00:00#)  but it will always use Dec 30th 1899 as the date.