community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!
SOLVED

Comparing range of Time for a single day

Meteoroid

I am trying to compare the range of time

i.e. if time between 8:00:00 am to 11:00:00 am then fill the column as "breakfast" , if time between 12:00:00 am to 16:00:00 am then fill the column as "lunch" else "dinner" using the formula tool. 

Approach is by converting the time value to number and then compare the range. The output it gives me in the row is 0,1,-1. 

Not sure if there is any other approach I could implement.

 

Thanks in advance

Bolide
Bolide

You've nailed this, there is no other method that would involve less tools, run quicker etc.

Magnetar
Magnetar

Hi @krishma04 ,

 

Slightly different approach. I like to start with a text table that lists start and end times for each meal

image.png

 

Then, I use the Generate Rows tool to generate all of the times in between, in seconds intervals. It runs SO quickly, even though it's generating thousands of records.

image.png

 

Join your original time field to the TimeMatch field. Whatever maps to a meal will come out of the J, and whatever doesn't will come out of the L.

 

image.pngWhat matchedimage.pngWhat didn't match


Let me know if this works for you.

 

Cheers!

Esther

Alteryx Certified Partner
Alteryx Certified Partner

Since you mentioned the numeric conversion process, I'll go ahead and how how that could be done so you have an alternative solution available. Field types and formatting are probably the most tricky thing to get sorted in these cases. If the ideas presented don't work in your application, share an example of the input date/time format and we can help get you on the right track.

 

I use two formula field to convert the date time value into an numeric hour value, then a second to classify. This could be combined into a single formula if you so desire. 

 

20190718-MealTime.png

Meteoroid
Thank you Esther, I'll try it out and see if it would work!

Krishma


This email and any attachments are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system immediately. If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
Meteoroid

Thanks Charlie, your solution worked perfectly! 

Got the desired output

 

Cheers!

Krishma

Atom

So when I use your method to fill in all times of the day with the specific time period, I have one issue.  When working at 20:00 (8PM) to Midnight (11:59:59).  I run into an issue with it loop continuously.   

Labels