Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Array of hour integers between two dates

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


Tags (2)
8 Replies
Partner
Partner

Re: Array of hour integers between two dates

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

Partner
Partner

Re: Array of hour integers between two dates

Just replace DT with your Date field,

regards

Not applicable

Re: Array of hour integers between two dates

Thanks Jamie, I am going to try that.

michael_gardner
Contributor III

Re: Array of hour integers between two dates

You can create an interval table with all possible combinations.


See attached.

Not applicable

Re: Array of hour integers between two dates

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.

Partner
Partner

Re: Array of hour integers between two dates

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

regards

Not applicable

Re: Array of hour integers between two dates

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.

michael_gardner
Contributor III

Re: Array of hour integers between two dates

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