Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
you can try this.
Where Time(frac([Creation Date]))<=MakeTime(1,0,0) or Time(frac([Creation Date]))>=MakeTime(17,0,0);
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';
Hi Anil, thank you for your reply. But it doesn't seem to load any data in spite of the data being present.
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.
The date is in the following format :
M/D/YYYY h:mm:ss[.fff] TT
That condition isn't working either. I am trying it in the load at the moment. Expression would work too.
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.
Hi Anil, please find attached the qvw file. [Creation Date] is the timestamp I was referring too.
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
No problem Anil. Attached is the base data