Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

amber2000
Contributor

Select between PickDate/StartTime &PickDate/StartTime with calendar object

Hello Qlikview wizards,

Can somebody help me figure out how I can use a Calendar object to select between 2 dates and times.

I already have a calendar object in use to select data between 2 dates with variables and this works fine.

='>=' & varFromDate & '<=' & varToDate

Now I want something similar to select data between date/time and date/time.

Example:

Select from (Pickdate) 14/03/2016 (StartTime)17:00:00 To (Pickdate)15/03/2016 (StartTime)17:00:00

I've used this as the searchstring: ='>=' & vFromDate & '>=' & vFromTime & '<=' & vToDate & '<=' & vToTime

Sadly it doesn't work at all.

Any idea what I'm doing wrong here please?

I'll send my qvw as an attach

Kind regards,

Monique

6 Replies
MVP & Luminary
MVP & Luminary

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

Well, this is not a valid search string: >=15/03/2016>=17:00:00<=16/03/2016<=17:00:00. It just doesn't make sense. Also you probably want something like >= 15/03/2016 17:00:00 and <= 16/03/2016 17:00:00. And you can't do that with separate date and time fields. You need a timestamp field. If you filter the time field with <=17:00:00 that will apply to all records. And that would filter out for example 15/03/2016 19:05:37


talk is cheap, supply exceeds demand
amber2000
Contributor

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

Hello Gysbert,

My Time field is actually a Timestamp field (in the load script I've transformed it to a time field).

So to get this TimeStamp I'm loading StartingTime again but without the format As StartingTime2

TimeStamp_StartingTime2.png

I'm not quit sure what you mean with the filter but is it possible to show it in my example?

Don't I need to use the Timestamp field as a variable?

Kind regards,

Monique

amber2000
Contributor

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

Hi everyone,

Does anybody have an idea please?

I've tried with the timestamp field but the variable value wont hold 15/03/2016 17:00:00.000 or 15/03/2016 17:00:00

Every time I want to set the Timestamp value in Settings --> Variable Overview and click OK the value is automaticly set back to 15/03/2016.

There must be a way to handle this in Qlikview???

Kind regards,

Monique

MVP & Luminary
MVP & Luminary

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

Add a timestamp field to your data model that's not formatted as Time. A numeric field would work well.


talk is cheap, supply exceeds demand
Not applicable

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

In Qlik, all the date and time stamp values are converted into numbers. You can directly use the field that have time stamp in your Calendar Object.

Not applicable

Re: Select between PickDate/StartTime &PickDate/StartTime with calendar object

In stead of Calendar object, use two input boxes for Start & End and select between values by trigger Action