topic Re: Time band in QlikView

Time band

arulsettu — Thu, 05 Nov 2015 05:48:40 GMT

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

Re: Time band

Anonymous — Thu, 05 Nov 2015 05:54:14 GMT

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!!

Re: Time band

Anonymous — Thu, 05 Nov 2015 05:55:12 GMT

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

Re: Time band

arulsettu — Thu, 05 Nov 2015 05:56:58 GMT

yes i checked

Re: Time band

Anonymous — Thu, 05 Nov 2015 05:59:03 GMT

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

Re: Time band

arulsettu — Thu, 05 Nov 2015 06:03:47 GMT

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

Re: Time band

Anonymous — Thu, 05 Nov 2015 06:10:25 GMT

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

Re: Time band

jonathandienst — Thu, 05 Nov 2015 06:10:46 GMT

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),

Re: Time band

jonathandienst — Thu, 05 Nov 2015 06:15:44 GMT

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;

Re: Time band

arulsettu — Thu, 05 Nov 2015 06:16:57 GMT

thank you very much jonathan dienst