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: 
mikegrattan
Creator III
Creator III

How to create Morning and Afternoon fields in data load based on time of day?

My Time field is formatted as follows:

time(Date#(textBetween(observation_time,'Last Updated on ', 'P'),'MMM DD YYYY, hh:mm tt')) as Time

I would like to create another field that can have the values of Morning or Afternoon, depending on the Time field.  If the time is between 04:00:00 AM and 11:59:59 AM, then it is morning.  If it is between 12:00:00 PM and 5:00:00 PM, then it is evening.

Help is appreciated very much.  Thank you.

 

Labels (3)
1 Solution

Accepted Solutions
Anonymous
Not applicable

You can build this as a master Item or in the data load:

= if( Time(Frac(observation_time))> '4:00:00 AM' and Time(Frac(observation_time))<'11:59:59 AM', 'Morning'
,if( Time(Frac(observation_time))> '12:00:00 PM' and Time(Frac(observation_time))< '5:00:00 PM', 'Evening','N/A'))

View solution in original post

4 Replies
Anonymous
Not applicable

if(Wildmatch(YourTimeField,'*AM*'),'Morning','Afternoon') as "Morning | Afternoon"

ft42_0-1594669084508.png (example with Now())

 

mikegrattan
Creator III
Creator III
Author

Thank you for your reply.  That looks like it would be great for generic AM/PM filtering but we wanted specific time frames for Morning vs Afternoon.  Is there another approach that would provide that functionality?

 

Anonymous
Not applicable

You can build this as a master Item or in the data load:

= if( Time(Frac(observation_time))> '4:00:00 AM' and Time(Frac(observation_time))<'11:59:59 AM', 'Morning'
,if( Time(Frac(observation_time))> '12:00:00 PM' and Time(Frac(observation_time))< '5:00:00 PM', 'Evening','N/A'))

mikegrattan
Creator III
Creator III
Author

That works; I was not aware of the Frac function so I learned something new today.  Thanks!