Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Displaying data between two time ranges

Dear All,

I have data available in a similar timestamp format : 11/2/2016  11:28:00 AM.

I have a requirement wherein I need to display records/data between a particular time range ie. 9:00 AM to 5:00 PM using the above timestamp.

Could you please with this query.

1 Solution

Accepted Solutions
mdmukramali
Specialist III
Specialist III

Hi,

you can try this.

Where Time(frac([Creation Date]))<=MakeTime(1,0,0) or Time(frac([Creation Date]))>=MakeTime(17,0,0);

View solution in original post

22 Replies
Anil_Babu_Samineni

May be this?

Load TimeStamp From Table

Where Date(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm TT') >= '09:00 AM' and Date(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT'), 'hh:mm TT') <= '05:00 PM';

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Hi Anil, thank you for your reply. But it doesn't seem to load any data in spite of the data being present.

jonathandienst
Partner - Champion III
Partner - Champion III

Is your date in DMY or MDY format? Do you want this in the load script or as an expression?

The condition should be like:

Where Frac(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT')) > 9/24 And Frac(Date#(TimeStamp, 'MM/DD/YYYY hh:mm:ss TT')) <= 17/24

9/24 and 17/24 represent the time limits as a fraction of a day.

You could simplify this by converting the timestamp string values to QV time/date values during the initial load.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

The date is in the following format :

M/D/YYYY h:mm:ss[.fff] TT

Anonymous
Not applicable
Author

That condition isn't working either. I am trying it in the load at the moment. Expression would work too.

Anil_Babu_Samineni

May be share sample data set, I've tested my sample. Looks working.. Can you work using below?

Date(Date#(TimeStamp, 'M/D/YYYY h:mm:ss[.fff] TT'), 'hh:mm TT') >= '09:00 AM' and Date(Date#(TimeStamp, 'M/D/YYYY h:mm:ss[.fff] TT'), 'hh:mm TT') <= '05:00 PM';


Note:- If none of the working, We would request you to share sample excel file that demon the issue.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Hi Anil, please find attached the qvw file. [Creation Date] is the timestamp I was referring too.

Anil_Babu_Samineni

Don't have access to open the app due to live project. So, will this possible to share excel file of data? So, Then i can test excel itself and will convert to Qlik

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

No problem Anil. Attached is the base data