any alternative of nested if condition for bucketing

[bhaskar_sm]

Dear All,

Do we have any alternative of nested if condition in terms of more efficient and optimized one for the bucketing

if(Hours <= 1 and Hours < 2, dual('1 AM-2 AM',1),
if(Hours <= 2 and Hours < 3, dual('2 AM-3 AM',2),
if(Hours <= 3 and Hours < 4, dual('3 AM-4 AM',3),
if(Hours <= 4 and Hours < 5, dual('4 AM-5 AM',4),
if(Hours <= 5 and Hours < 6, dual('5 AM-6 AM',5),
if(Hours <= 6 and Hours < 7, dual('6 AM-7 AM',6),
if(Hours <= 7 and Hours < 8, dual('7 AM-8 AM',7),
if(Hours <= 8 and Hours < 9, dual('8 AM-9 AM',8),
if(Hours <= 9 and Hours < 10, dual('9 AM-10 AM',9),
if(Hours <= 10 and Hours < 11, dual('10 AM-11 AM',10),
if(Hours <= 11 and Hours < 12, dual('11 AM-12 PM',11),
if(Hours <= 12 and Hours < 13, dual('12 PM-1 PM',12),
if(Hours <= 13 and Hours < 14, dual('1 PM-2 PM',13),
if(Hours <= 14 and Hours < 15, dual('2 PM-3 PM',14),
if(Hours <= 15 and Hours < 16, dual('3 PM-4 PM',15),
if(Hours <= 16 and Hours < 17, dual('4 PM-5 PM',16),
if(Hours <= 17 and Hours < 18, dual('5 PM-6 PM',17),
if(Hours <= 18 and Hours < 19, dual('6 PM-7 PM',18),
if(Hours <= 19 and Hours < 20, dual('7 PM-8 PM',19),
if(Hours <= 20 and Hours < 21, dual('8 PM-9 PM',20),
if(Hours <= 21 and Hours < 22, dual('9 PM-10 PM',21),
if(Hours <= 22 and Hours < 23, dual('10 PM-11 PM',22),
if(Hours <= 23 and Hours < 24, dual('11 PM-12 AM',23)
))))))))))))))))))))))) as TimeBucket,

Thanks,

BK

[Vegar · Accepted solution]

Assuming that you have a field containing time, then do something like this:

Dual(Time(TimeField, 'h TT')  &'-'& Time(TimeField + date#(1,h), ' h TT'), hour(TimeField)) as Bucket

[Saravanan_Desingh]

One solution is.

tab1:
LOAD Dual(Time((IterNo()-1)/24,'h TT')&'-'& Time((IterNo())/24,'h TT'),IterNo()) As TimeBucket
AutoGenerate 1
While IterNo()<=12;

[Saravanan_Desingh]