Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to filter values by date and time

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.DateName Table.Value
08/31/2011 00.00.00423
08/31/2011 00.10.005287
08/31/2011 00.20.00 (and go on...)2857
09/01/2011 00.00.0036
09/01/2011 00.10.00789

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

Tags (1)
1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

How to filter values by date and time

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

2 Replies
fosuzuki
Valued Contributor II

How to filter values by date and time

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

Not applicable

Re: How to filter values by date and time

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

Community Browser