Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Comparing range of Time for a single day

krishma04
6 - 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

7 REPLIES 7
rafalolbert
ACE Emeritus
ACE Emeritus

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

estherb47
15 - Aurora
15 - Aurora

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.

 

What matchedWhat matchedWhat didn't matchWhat didn't match


Let me know if this works for you.

 

Cheers!

Esther

CharlieS
17 - Castor
17 - Castor

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

krishma04
6 - 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.
krishma04
6 - Meteoroid

Thanks Charlie, your solution worked perfectly! 

Got the desired output 😄

 

Cheers!

Krishma

DauteriveA
5 - 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.   

RichardChu
5 - Atom

@CharlieS Good solution! 

Labels