Find the best date based on criteria

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,

Allocate pre and post work.yxzp

Accepted answers

RolandSchubert

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

Allocate pre and post work_sample.yxmd

RolandSchubert

Hi @joannasokolowska ,

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

Best,

Roland

Allocate pre and post work_sample.yxmd

All comments

RolandSchubert

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

Allocate pre and post work_sample.yxmd

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".

Allocate pre and post work.yxmd

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!!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703