Make Time Function With IF Statement Issue

dineshm030 · ‎2021-07-02

Hi All,

I creating the Sequence number for DateTime as hour to hour in 24 hour format. If time is between 05:01 - 06:00 then Sequence number should be as 1 as so on. Below is the script using:

If(Time(Frac(DateTimeUTC),'hh') > maketime(05) and Time(Frac(DateTimeUTC),'hh') <= maketime(06),'1',
If(Time(Frac(DateTimeUTC),'hh') > maketime(06) and Time(Frac(DateTimeUTC),'hh') <= maketime(07),'2',
If(Time(Frac(DateTimeUTC),'hh') > maketime(07) and Time(Frac(DateTimeUTC),'hh') <= maketime(08),'3',
If(Time(Frac(DateTimeUTC),'hh') > maketime(08) and Time(Frac(DateTimeUTC),'hh') <= maketime(09),'4',
If(Time(Frac(DateTimeUTC),'hh') > maketime(09) and Time(Frac(DateTimeUTC),'hh') <= maketime(10),'5',
If(Time(Frac(DateTimeUTC),'hh') > maketime(10) and Time(Frac(DateTimeUTC),'hh') <= maketime(11),'6',
If(Time(Frac(DateTimeUTC),'hh') > maketime(11) and Time(Frac(DateTimeUTC),'hh') <= maketime(12),'7',
If(Time(Frac(DateTimeUTC),'hh') > maketime(12) and Time(Frac(DateTimeUTC),'hh') <= maketime(13),'8',
If(Time(Frac(DateTimeUTC),'hh') > maketime(13) and Time(Frac(DateTimeUTC),'hh') <= maketime(14),'9',
If(Time(Frac(DateTimeUTC),'hh') > maketime(14) and Time(Frac(DateTimeUTC),'hh') <= maketime(15),'10',
If(Time(Frac(DateTimeUTC),'hh') > maketime(15) and Time(Frac(DateTimeUTC),'hh') <= maketime(15,30),'11',
If(Time(Frac(DateTimeUTC),'hh') > maketime(15,30) and Time(Frac(DateTimeUTC),'hh') <= maketime(16),'12',
If(Time(Frac(DateTimeUTC),'hh') > maketime(16) and Time(Frac(DateTimeUTC),'hh') <= maketime(17),'13',
If(Time(Frac(DateTimeUTC),'hh') > maketime(17) and Time(Frac(DateTimeUTC),'hh') <= maketime(18),'14',
If(Time(Frac(DateTimeUTC),'hh') > maketime(18) and Time(Frac(DateTimeUTC),'hh') <= maketime(19),'15',
If(Time(Frac(DateTimeUTC),'hh') > maketime(19) and Time(Frac(DateTimeUTC),'hh') <= maketime(20),'16',
If(Time(Frac(DateTimeUTC),'hh') > maketime(20) and Time(Frac(DateTimeUTC),'hh') <= maketime(21),'17',
If(Time(Frac(DateTimeUTC),'hh') > maketime(21) and Time(Frac(DateTimeUTC),'hh') <= maketime(22),'18',
If(Time(Frac(DateTimeUTC),'hh') > maketime(22) and Time(Frac(DateTimeUTC),'hh') <= maketime(23),'19',
If(Time(Frac(DateTimeUTC),'hh') > maketime(23) and Time(Frac(DateTimeUTC),'hh') <= maketime(00),'20',
If(Time(Frac(DateTimeUTC),'hh') > maketime(00) and Time(Frac(DateTimeUTC),'hh') <= maketime(01),'21',
If(Time(Frac(DateTimeUTC),'hh') > maketime(01) and Time(Frac(DateTimeUTC),'hh') <= maketime(02),'22',
If(Time(Frac(DateTimeUTC),'hh') > maketime(02) and Time(Frac(DateTimeUTC),'hh') <= maketime(02,30),'23',
If(Time(Frac(DateTimeUTC),'hh') > maketime(02,30) and Time(Frac(DateTimeUTC),'hh') <= maketime(03),'24', '25')))))))))))))))))))))))) as Sequence

I am not getting sequence number is 20. Below is the screenshot for you ref. I have a data time between 23 to 00 hours.

Thank in advance. @MayilVahanan @sunny_talwar @swuehl Kindly help me out.

Regards,

Dinesh.

rubenmarin · ‎2021-07-03

Hi, you can try with <=24, because there is no number higher than 23 and lower than 0, or just keep the >23 comparison and remove the <= comparison

General Question