-
1. Re: Expect then to match if at character 0
John Sobczak Dec 14, 2017 9:49 AM (in response to Cheryl Morgan)1 of 1 people found this helpfulThe 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.
-
2. Re: Expect then to match if at character 0
Cheryl Morgan Dec 14, 2017 10:07 AM (in response to John Sobczak)1 of 1 people found this helpfulSince 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.
-
3. Re: Expect then to match if at character 0
John Sobczak Dec 14, 2017 10:10 AM (in response to Cheryl Morgan)1 of 1 people found this helpfulCheryl, 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.
-
4. Re: Expect then to match if at character 0
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 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 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.