Time band

[arulsettu]

Hi,

     i am trying to create time band based on a date field

time(Frac(MPA_CR_DT)) as PRE_APPROVED_CREATED_DATE_TIME

able to get the time field like this

12:00:00 AM

to create a time band i used following code

if(PRE_APPROVED_CREATED_DATE_TIME>='6:00:00 AM' and PRE_APPROVED_CREATED_DATE_TIME<='9:00:00 AM',Dual('6-9',1),

if(PRE_APPROVED_CREATED_DATE_TIME>='9:00:00 AM' and PRE_APPROVED_CREATED_DATE_TIME<='12:00:00 PM',Dual('9-12',2),

if(PRE_APPROVED_CREATED_DATE_TIME>='12:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='2:00:00 PM',Dual('12-2',3),

if(PRE_APPROVED_CREATED_DATE_TIME>='2:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='5:00:00 PM',Dual('2-5',4),

if(PRE_APPROVED_CREATED_DATE_TIME>='5:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='8:00:00 PM',Dual('5-8',5),

if(PRE_APPROVED_CREATED_DATE_TIME>='8:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='11:00:00 PM',Dual('8-11',6),

if(PRE_APPROVED_CREATED_DATE_TIME>='11:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='1:00:00 AM',Dual('11-1',7),

if(PRE_APPROVED_CREATED_DATE_TIME>='1:00:00 AM' and PRE_APPROVED_CREATED_DATE_TIME<='6:00:00 AM',Dual('1-6',8)

)))))))) as Time_band;

everything is showing except 11-1 time band.

11 PM to 1 AM is only not showing.

can anyone help me on this

Thanks

[Anonymous]

Why you are using '=' again. it wud replicate your data in both the interval:

like it shud be:

if(PRE_APPROVED_CREATED_DATE_TIME>='6:00:00 AM' and

PRE_APPROVED_CREATED_DATE_TIME<='9:00:00 AM',Dual('6-9',1),

if(PRE_APPROVED_CREATED_DATE_TIME>'9:00:00 AM' and

PRE_APPROVED_CREATED_DATE_TIME<='12:00:00 PM',Dual('9-12',2),

not this which you have done:

if(PRE_APPROVED_CREATED_DATE_TIME>='6:00:00 AM' and

PRE_APPROVED_CREATED_DATE_TIME<='9:00:00 AM',Dual('6-9',1),

if(PRE_APPROVED_CREATED_DATE_TIME>='9:00:00 AM' and   --Remove '='

PRE_APPROVED_CREATED_DATE_TIME<='12:00:00 PM',Dual('9-12',2)

Hope this will help!!

[Anonymous]

Do you have value of 11-1 PM in your field?

[arulsettu]

yes i checked

[Anonymous]

Post the sample QVW & Remove this '=' which you are repeating in your expression each and every time...

[arulsettu]

'=' does not cause any issue other bands are showing.

[Anonymous]

I know, it is not creating any issue but it is replication your same data again and again...

[jonathandienst · Accepted solution]

if(PRE_APPROVED_CREATED_DATE_TIME>='11:00:00 PM' and PRE_APPROVED_CREATED_DATE_TIME<='1:00:00 AM',Dual('11-1',7),

These two conditions are mutually exclusive. 11:00 PM has numeric value 0.958 and 1:00 am has value 0.0417 because this time range spans the day boundary. Change this if() to:

if(PRE_APPROVED_CREATED_DATE_TIME>='11:00:00 PM' OR PRE_APPROVED_CREATED_DATE_TIME<='1:00:00 AM',Dual('11-1',7),

[jonathandienst]

You can simplify the expression with:

if(PRE_APPROVED_CREATED_DATE_TIME <= '1:00:00 AM',  Dual('11-1',7),

if(PRE_APPROVED_CREATED_DATE_TIME <= '6:00:00 AM',  Dual('1-6',8),

if(PRE_APPROVED_CREATED_DATE_TIME <= '9:00:00 AM',  Dual('6-9',1),

if(PRE_APPROVED_CREATED_DATE_TIME <= '12:00:00 PM', Dual('9-12',2),

if(PRE_APPROVED_CREATED_DATE_TIME <= '2:00:00 PM',  Dual('12-2',3),

if(PRE_APPROVED_CREATED_DATE_TIME <= '5:00:00 PM',  Dual('2-5',4),

if(PRE_APPROVED_CREATED_DATE_TIME <= '8:00:00 PM',  Dual('5-8',5),

if(PRE_APPROVED_CREATED_DATE_TIME <= '11:00:00 PM', Dual('8-11',6),

Dual('11-1',7),

)))))))) as Time_band;

[arulsettu]

thank you very much  jonathan dienst