topic How to filter values by date and time in QlikView

How to filter values by date and time

Thu, 01 Dec 2011 16:46:06 GMT

Hi all,

A table has around 200.000 records; the columns are date & time and values (a value every 10 minutes).

The result is similar to:

date (mm/dd/yyyy) & time (hh:mm:ss)	value
Name Table.Date	Name Table.Value
08/31/2011 00.00.00	423
08/31/2011 00.10.00	5287
08/31/2011 00.20.00 (and go on...)	2857
09/01/2011 00.00.00	36
09/01/2011 00.10.00	789

I need to filter by date, eg from 1st september 2011 to 30th september 2011. Then I have to filter again for time, eg from 00.00.00 to 03.00.00.

I'm trying to implemet a macro, using 4 input fields (dateFrom, dateTo, TimeFrom, TimeTo) and a button to invoke the macro. No any validate data is necessary in this case.

This is my first macro code:

sub [Filter]

rem trying to set value input field into a variable...

set dateFrom = ActiveDocument.Variables("dateFrom")

set dateTo = ActiveDocument.Variables("dateTo")

rem trying to apply the filter, but it seems it doesn't work

ActiveDocument.Fields("Name Table.Date").Select ">$(dateFrom) 00.00.00"

ActiveDocument.Fields("Name Table.Date").ToggleSelect ">$(dateTo) 11.00.00"

rem trying to apply the filter, it seems it works

ActiveDocument.Fields("Name Table.Date").Select ">09/01/2011 00.00.00"

ActiveDocument.Fields("Name Table.Date").ToggleSelect ">09/01/2011 11.00.00"

end sub

How can I implement this filter? How can I fix this macro?

Just ask questions for further infos...

Thanks for helping

How to filter values by date and time

fosuzuki — Thu, 01 Dec 2011 17:17:05 GMT

Hi,

instead of doing this:

set dateFrom = ActiveDocument.Variables("dateFrom")

set dateTo = ActiveDocument.Variables("dateTo")

ActiveDocument.Fields("Name Table.Date").Select ">$(dateFrom) 00.00.00"

ActiveDocument.Fields("Name Table.Date").ToggleSelect ">$(dateTo) 11.00.00"

Try this:

dateFrom = ActiveDocument.Variables("dateFrom").GetContent.String

dateTo = ActiveDocument.Variables("dateTo").GetContent.String

ActiveDocument.Fields("Name Table.Date").Select ">" & dateFrom & " 00.00.00"

ActiveDocument.Fields("Name Table.Date").ToggleSelect ">" & dateTo & " 11.00.00"

* In VB Script, you cannot use DollarSignExpansion.

Hope this helps you.

Regards,

Fernando

Re: How to filter values by date and time

Fri, 02 Dec 2011 11:21:35 GMT

Thank you for your help.

I was able to develop the little macro from your example and added another filter by time the same system

My table has two columns, one for date and another one for time; macro will run first select (from date to date) and will run a second select on first result set (from time to time).