Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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


8 Replies
Partner
Partner

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

Just replace DT with your Date field,

regards

Not applicable

Thanks Jamie, I am going to try that.

Creator III
Creator III

You can create an interval table with all possible combinations.


See attached.

Not applicable

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

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

regards

Not applicable

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.

Creator III
Creator III

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