Solved: Re: Displaying data between two time ranges - Qlik Community

Anonymous · ‎2018-01-09

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.

mdmukramali · ‎2018-01-10

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

Anil_Babu_Samineni · ‎2018-01-09

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';

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-01-09

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

jonathandienst · ‎2018-01-10

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 · ‎2018-01-10

The date is in the following format :

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

Anonymous · ‎2018-01-10

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

Anil_Babu_Samineni · ‎2018-01-10

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.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-01-10

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

Anil_Babu_Samineni · ‎2018-01-10

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-01-10

No problem Anil. Attached is the base data