topic Re: How to search by DateTime dimension in QlikView

How to search by DateTime dimension

sjhussain — Wed, 15 Jul 2015 15:05:06 GMT

Hello,

We have a dimension YYYYMMDDHHmmss and want to search the "TIME" part of it through variables.

for eg. varS1FromTime and varS1ToTime

We want to be able to filter a table using the TIME Variables.

varS1FromTime: 7:30

varS1ToTime: 11:00

This would filter the table time from 7:30am to 11:00am

How can we setup the time dimension in the load script and use it in the Variable Event Triggers.

Thanks in advance.

Message was edited by: Syed Hussain

Re: How to search by DateTime dimension

Wed, 15 Jul 2015 15:25:52 GMT

Hi Can u pls give more info..or else u can give sample qvw

Re: How to search by DateTime dimension

sjhussain — Wed, 15 Jul 2015 16:36:05 GMT

Narendra,

I have attached the image of the table and the log.cvs file containing the date in the main question.

Will appreciate your assistance.

Thanks.

Re: How to search by DateTime dimension

rwunderlich — Wed, 15 Jul 2015 22:55:25 GMT

Create a time dimension in the script by extracting the time portion from the timestamp:

time(frac(timestamp#(log_datetimeindata, 'YYYYMMDDHHmmss'))) as Time

-Rib

Re: How to search by DateTime dimension

sjhussain — Thu, 16 Jul 2015 01:26:50 GMT

Rob,

Thanks for the response.

time(frac(timestamp#(log_datetimeindata, 'YYYYMMDDHHmmss'))) as log_time

How would I use the log_time Dimension in the variable trigger?

Field: log_time

='>=' & $(varS1FromTime) & '<=' & $(varS1ToTime)

Also how would I enter the varS1FromTime and varS1ToTime variables? 7:30 and 11:00?

Thanks

Re: How to search by DateTime dimension

rwunderlich — Thu, 16 Jul 2015 01:32:21 GMT

I could potentially work up an answer to your question, but first I would ask why not use a listbox or slider against the Time field and let the user select a range that way? Much easier I would think than using variables and triggers.

-Rob

Re: How to search by DateTime dimension

sjhussain — Thu, 16 Jul 2015 07:05:50 GMT

Rob,

We have to use the input box as we are going to using multiple input boxes for multiple time search and then combine them into a boolean expression to get our result.

This will give the end user the liberty to search through mutiple timeframes. For example

vS1TimeFrom: 7:30

vS1TimeTo: 11:00

and/or

vS2TimeFrom: 13:00

vS2TimeTo: 15:00

and then we create a boolean expression with and/or and pass it in set analysis.

So we need to have input box and enter the time.

Will appreciate your assistance on how to search between dates using the Time function you wrote in the previous message.

Thanks again.

Re: How to search by DateTime dimension

rwunderlich — Fri, 17 Jul 2015 02:07:24 GMT

I don't think you want to search between dates, but rather the new time field. Set the Number format of the input box to "Time" and require the user to enter times like "7:30", including the :.

-Rob