Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arulsettu
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
jonathandienst
Partner - Champion III
Partner - Champion III

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

balrajahlawat
Champion
Champion

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

arulsettu
Master III
Master III
Author

yes i checked

balrajahlawat
Champion
Champion

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

arulsettu
Master III
Master III
Author

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

balrajahlawat
Champion
Champion

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
arulsettu
Master III
Master III
Author

thank you very much  jonathan dienst