topic Array of hour integers between two dates in QlikView

Array of hour integers between two dates

Tue, 24 Mar 2015 18:13:49 GMT

Hello everyone,

Does anyone know of a way to find if an hour exists between two dates? For example:

Start Date = 03/18/2015 03:00: PM

End Date = 03/19/2015 07:00 AM

So how could one test to find it the hour of 08 (8 am) falls between these two dates?

Sincere thanks for any help.

Sheldon

Re: Array of hour integers between two dates

jaimeaguilar — Tue, 24 Mar 2015 18:33:23 GMT

You can create a field in script like this:

time(maketime(hour(DT)),'hh TT') as Hour

this way you'll have a field that contains: 12 am, 1 am , 2 am and so on (you'll have up to 24 possible values, according to every hour in a day), so you can filter an ocurrence in time, rounded to hours

hope this helps,

regards

Re: Array of hour integers between two dates

jaimeaguilar — Tue, 24 Mar 2015 18:34:08 GMT

Just replace DT with your Date field,

regards

Re: Array of hour integers between two dates

Tue, 24 Mar 2015 18:43:07 GMT

Thanks Jamie, I am going to try that.

Re: Array of hour integers between two dates

Anonymous — Tue, 24 Mar 2015 19:45:31 GMT

You can create an interval table with all possible combinations.

See attached.

Re: Array of hour integers between two dates

Tue, 24 Mar 2015 19:48:19 GMT

Thanks Jamie that was helpful, but what I am really looking for is a way to use the match function to test if the hour exists between two dates.

Re: Array of hour integers between two dates

jaimeaguilar — Tue, 24 Mar 2015 21:47:16 GMT

Ok, then try with interval match function, just like Michael suggested,

regards

Re: Array of hour integers between two dates

Wed, 25 Mar 2015 01:18:38 GMT

Thanks Mike, this is almost what I am looking for. The qvw you attached is very clear. What I am trying to achieve is the time value of the hour intervals between two dates. For instance their are nine hourly intervals between 03/18/2015 08:00 PM and 03/19/2015 05:00 AM. The interval Function returns them as 1,2,3,4,5,6,7,8,9. What I am looking for is something that will return the hour intervals as the actual hour values, like 20, 21,22,23,00,01,02,03,04,05.

Re: Array of hour integers between two dates

Anonymous — Wed, 25 Mar 2015 03:06:55 GMT

I'm sure there is a better way to do this but it seems to work.