topic Select between PickDate/StartTime &PickDate/StartTime with calendar object in QlikView

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

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Gysbert_Wassenaar — Wed, 23 Mar 2016 17:01:57 GMT

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

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

amber2000 — Wed, 23 Mar 2016 17:26:47 GMT

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

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

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

amber2000 — Thu, 24 Mar 2016 14:31:41 GMT

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

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

Gysbert_Wassenaar — Thu, 24 Mar 2016 17:16:40 GMT

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

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

Thu, 24 Mar 2016 17:32:40 GMT

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.

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

Thu, 24 Mar 2016 17:33:43 GMT

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