Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master III
Master III

Time band

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

1 Solution

Accepted Solutions
Highlighted

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






Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

9 Replies
Highlighted
Champion
Champion

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

Highlighted
Champion
Champion

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

Highlighted
Master III
Master III

yes i checked

Highlighted
Champion
Champion

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

Highlighted
Master III
Master III

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

Highlighted
Champion
Champion

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

Highlighted

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






Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Master III
Master III

thank you very much  jonathan dienst