topic Re: where clause for time in QlikView

where clause for time

Wed, 29 Jan 2014 17:16:10 GMT

Hi,

I'm doing a simple where clause on sales_time

Where sales_time < '11:59:59.000'

sample of sales_time values

02:39:14.000

02:40:40.000

02:47:32.000

04:00:00.000

04:01:28.000

04:45:02.000

04:50:00.000

05:00:00.000

and it returns 0 lines.

i tried num(sales_time ) or time(sales_time) or timestamp(sales_time) or num(timestamp(sales_time)), none of it works.

help please ....

Re: where clause for time

Anonymous — Wed, 29 Jan 2014 17:21:17 GMT

where time(time#(sales_time, 'hh:mm:ss.fff' ) < time(time#(sales_time, '11:59:59.000' , 'hh:mm:ss.fff' )

Re: where clause for time

Wed, 29 Jan 2014 17:21:18 GMT

Hi,

I would use the hour(), minute() and second() functions

sth like

WHERE hour(sales_time)*3600+minute(sales_time)*60+second(sales_time) < 11*3600+59*60+60

Fabrice

Re: where clause for time

luis_pimentel — Wed, 29 Jan 2014 17:21:21 GMT

Make sure your TimeFormat definition looks like:

SET TimeFormat='hh:mm:ss[.fff]';

Then, the comparision should work.

Re: where clause for time

maxgro — Wed, 29 Jan 2014 19:34:13 GMT

it seems your filter works for me, with this

Where sales_time < '02:59:59.000';

I get 3 rows,

Here the script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

load * Where sales_time < '02:59:59.000';

//Where sales_time < '11:59:59.000'

//sample of sales_time values

LOAD * INLINE [

sales_time

02:39:14.000

02:40:40.000

02:47:32.000

04:00:00.000

04:01:28.000

04:45:02.000

04:50:00.000

05:00:00.000

];

Re: where clause for time

Thu, 30 Jan 2014 08:49:56 GMT

Bill,

You have the closest solution.

but with this sequence time#(time()).

thx

Re: where clause for time

Thu, 30 Jan 2014 08:51:17 GMT

Hi Fabrice,

Yes, it'd work that way, and i considered it as "best practice". thanks.

Re: where clause for time

Thu, 30 Jan 2014 09:19:29 GMT

Try this

where sales_time< time(time#(sales_time, '11:59:59.000' , 'hh:mm:ss.fff' )