topic condition in QlikView

condition

master_student — Fri, 23 Sep 2016 11:30:32 GMT

Hello

I need to set a condition in my expression to display the time only if it is >13:00:00 and <14:00:00

If(time(DATE_DEB)>'13:00:00', and time(DATE_DEB)>'13:00:00', '')

DATE_DEB has this format : 12/01/2016 12:32:55

any idea please

Re: condition

marcus_sommer — Fri, 23 Sep 2016 11:37:19 GMT

I would use hour/minute/second as fields within the datamodel. You could simply use:

hour(DATE_DEB) as Hour

or add a master timetable: The Master Time Table

and from this Hour your could create a flag-field, like:

if(hour(DATE_DEB) = 13, 1, 0) as HourFlag

or using Hour within a set analysis, like:

sum({< Hour = {13}>} value)

- Marcus

Re: condition

sunny_talwar — Fri, 23 Sep 2016 11:37:34 GMT

May be this:

If(Frac(TimeStamp#(DATE_DEB, 'DD/MM/YYYY')) > MakeTime(13) and Frac(TimeStamp#(DATE_DEB, 'DD/MM/YYYY'))< MakeTime(14), Time(TimeStamp#(DATE_DEB, 'DD/MM/YYYY')))

Re: condition

holmlund — Fri, 23 Sep 2016 11:42:05 GMT

If(Frac(DATE_DEB)> (13/24) and Frac(DATE_DEB)<(14/24), Time(DATE_DEB))

Re: condition

master_student — Fri, 23 Sep 2016 12:13:11 GMT

nothing as a result

Re: condition

master_student — Fri, 23 Sep 2016 12:38:10 GMT

did as you said , if (HourFlag=1,Hour) but i get result like that :

Re: condition

marcus_sommer — Fri, 23 Sep 2016 12:57:29 GMT

The field Hour looked like a time and not a hour. How is Hour and HourFlag created?

- Marcus

Re: condition

Fri, 23 Sep 2016 13:23:16 GMT

Hi,

Can you please try below and let me know if it is fine.

if(replace(mid(a, Index(a,' ')+1),':','')>130000 and replace(mid(a, Index(a,' ')+1),':','')<140000,'',a)

Here, 'a' is the column

Thanks,
Sreeman

Re: condition

miguelbraga — Fri, 23 Sep 2016 13:49:34 GMT

Hey there mate,

If you can make changes in the script you can make it something like this:

Table:

LOAD *,

Flag_Greater13 * Flag_Lesser14 as Flag

LOAD *,

if(hour(DATE_DEB) > 13, 1, 0) as Flag_Greater13,

if(hour(DATE_DEB) < 14, 1, 0) as Flag_Lesser14;

LOAD *

FROM [your connection to your database goes here];

Then use the Flag field in your set analysis like this:

=sum({$< Flag= {1}>} value)

This is by far the best option because you bring the complexity to the script which increases the rendering perfomance in your table if you have millions of rows and it's easy to understand

Best regards,

D.A. MB

Re: condition

master_student — Fri, 23 Sep 2016 13:52:10 GMT

i have to use time not hour

Re: condition

marcus_sommer — Fri, 23 Sep 2016 13:54:52 GMT

To display time as dimension within the chart didn't meant that you couldn't create hour/minute and so on and maybe some flags within the script and to use these fields within the chart-expressions.

- Marcus

Re: condition

miguelbraga — Fri, 23 Sep 2016 14:04:19 GMT

If so make this change:

Table:

LOAD *,

Flag_Greater13 * Flag_Lesser14 as Flag;

LOAD *,

if(

time( timestamp(DATE_DEB,'DD/MM/YYYY hh:mm:ss') )

time( timestamp(date(floor(DATE_DEB) ,'DD/MM/YYYY') + time(MakeTime(13),'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),

1, 0) ) as Flag_Greater13,

if(

time( timestamp(DATE_DEB,'DD/MM/YYYY hh:mm:ss') )

time( timestamp(date(floor(DATE_DEB) ,'DD/MM/YYYY') + time(MakeTime(14),'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss'),

1, 0) ) as Flag_Lesser14;

LOAD *

FROM [your connection to your database goes here];