Find the best date based on criteria

[joannasokolowska]

Dear Colleagues,

I would like to ask you for help with the following case. I have 2 data sources (attached in the altreryx workflow) 

1. Date of the event , which includes information on which date exact users are attending the event

2. Calendar bookings , which includes information on which dates exact users are available (booked=opposite to available) 

I would like to connect both data and:

1. Find date for doing pre-work before the event - Check the best date (available) closest and before the event date , max within 2 weeks before the event. 

2. Find date for doing post-work after the event - Check the best date (available) closest and after the event date , max within a month after the event. 

Thanks,

[RolandSchubert · Accepted solution]

Hi @joannasokolowska ,

I think, joining events and user availability and filtering by avalability status and dates should solve the problem. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

[zajaccount]

Hi Joanna,

please find the suggested solution attached - let me know if this works for you.

I first joined the two datasets by the user. After this, I split the joined into to flows, selecting the start and the end date columns. Later I filtered for the availability and calculated the difference between the "day" and the start/end date.

After this, I filtered the difference to be greater or less than 0. depending on whether we I am calculating the prework or the postwork dates. After this, I summarized my flows to group by event name, user and show the min/max difference between the dates. After that I joined the summarized tables to get the remaining data back and finally joined the two flows together to form a final output file:

I am not sure what's the desired outcome when no date is availablie within the two weeks/1 month. Therefore, I added a column to check whether the dates meet the criteria - if the result was false, the suggested date would not meet the criteria, however would still be the "best of the rest".

[joannasokolowska]

Thank you for help! It works as expected 🙂 

Can also ask you what should I change to check only weekdays (excluding Saturday/Sunday) 

Thanks,

Asia

[joannasokolowska]

Thank you for your help!!! 

[zajaccount]

Hi

you could add a formula tool to check whether the [Day] is Saturday or Sunday and then a filter to Filter out the weekend days. Please find the adjusted workflow

[RolandSchubert · Accepted solution]

Hi @joannasokolowska ,

i've added a filter to remove weekend days from the "availability" list of days. 

Best,

Roland