Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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),
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!!
Do you have value of 11-1 PM in your field?
yes i checked
Post the sample QVW & Remove this '=' which you are repeating in your expression each and every time...
'=' does not cause any issue other bands are showing.
I know, it is not creating any issue but it is replication your same data again and again...
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),
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;
thank you very much jonathan dienst