Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
jackm75
New Contributor

Filter between two time variables

I'm sure this is simple, but I just can't get it to work.  I have an Input Box with two variables: StartTime and EndTime.  I have a button that on click should filter the data to only show the records between the two time variables that have been input into the box.  In the Actions of the button, I have 'Select in Field' - 'Field' is "ActTime" and 'Search String' is "='>=' & StartTime & '<=' EndTime".   When I click the button, nothing happens.  What can I do differently?

6 Replies

Re: Filter between two time variables

Hi,

Probably checking that the format in fields and the format returned by the variables is the same would be enough. When using slider/calendar objects or input boxes, values are stored as numeric, while the match in the field is done using the literal, I'd bet on something like this in the search string of the action

='>=' & Time(StartTime) & '<=' Time(EndTime)

Time() only returns the time part of a complete timestamp, so if the input data of Start Time is "31/01/2012 16:54:32" Time() will return "16:54:32". In QlikView, 24 hours (one day) has a numeric value of 1, and 12 hours have a numeric value of 0.5 and so. Variables might be storing something like "0.59976".

Hope that helps.

Miguel

jackm75
New Contributor

Filter between two time variables

Thanks for your reply Miguel.  I should have also mentioned that in my load script, I am getting 'ActTime' by pulling from a full timestamp:  Time(ACTVDATE, 'h:mm:ss') as ActTime.  In the Input Box I have the Format Pattern set to Time h:mm:ss.  Trying what you suggested didn't seem to help.  Since you mentioned formatting should be the same, I changed to Time(ACTVDATE, 'h:mm:ss TT') as ActTime in load to match the TimeFormat load variable.  And the Input Box to h:mm:ss TT, but still no help.  However, ='>=' & StartTime & '<=' EndTime is selecting all times rather than nothing at all.

Re: Filter between two time variables

Hi,

Check the application attached. I'm using the same format in the field definition in the script, the variables and the search string using Time() function and returns as expected.

Hope that helps.

Miguel

jackm75
New Contributor

Filter between two time variables

Thanks for the example.  I made mine look like yours and I'm still not having any luck.  It should be as simple as that, I'm just not seeing what the issue is.   For now, I've put a list box with the time values so they can be selected as needed.  Not the solution I want as there can be 100's of values to select, but it will serve the purpose until I can get this figured out.   Thanks again.

Re: Filter between two time variables

Jack,

Can you upload a sample file with dummy rows and the script (at least the part where the times are generated) so we can see where the issue is?

Regards.

Miguel

jackm75
New Contributor

Filter between two time variables

I took a slightly different approach and got it to work.  I created a TimeValue field on load:  Mid(Num(ACTVDATE)6,12) as TimeValue.  This returned just the time integer value.  Then on my button click I did this string based on the TimeValue field:  ='>=' & Num(Time(vStartTime)) & '<=' & Num(Time(vEndTime)).  It converts the time entered into the Input box into a number and the evaluation works.