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

Alteryx designer Discussions

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

Find available time between End and Start Dates

Atom

Not sure if this is doable but i would like to look at all the star and end date highlighted in the picture below and look for differences between each End and Start date to see when a person is available an not assigned to any projects. I would like the date range to be between 01/01/2019 and 09/30/2019 but the range will be expanded or changed as time goes by.

 

For example if a person is assigned to a project with the first set of start and end dates is 01/15/2019-02/01/2019 and second set is 02/15/2019-02/20/2019, the available date range would be 01/01/2019-01/14/2019, 02/02/2019-02/14/2019, 02/21/2019-09/30/2019.

 

Before1.jpg

The first picture has multiple records for each auditor but in he output i would like to have only one record for each as shown in the 2nd picture.

 

After1.jpg

 

I am attaching a sample file as well.

 

Thank you in advance for any help or tips.

 

 

Alteryx
Alteryx

@DomMiro 

 

Generate Rows tool is your friend here. You can use it to generate every available date between two dates, use the join tool to match it against the period you're looking for and isolate out the booked out dates. I've attached a sample of how this could be used.

 

availability.png

 

Alteryx
Alteryx

Hi @DomMiro 

 

I have looked at this in a similar way to Michal, but attempting to replicate the table you have in your after tab. By summarizing for each auditor their minimum values for each period, you can then Transpose and Cross-Tab your data back together to have only one line for each auditor and just displaying the dates they are available. The formula tool is then finding the date before or after your current dates they are busy

 

The only thing to note in this process is that you will be using the Select tool to re-order and rename your fields into the order and naming conventions you are after. This is because for example where we have "End1" in the original dataset, this needs to now be the "Start1" Field. 

 

Similarly, in the final Formula tool, you would need to manually enter the start date for your period. This is just a case of amending the Formula field

 

 

Alteryx Certified Partner

Hi @DomMiro 

 

Attached is a solution that produces the output you specified.

 

image.png

 

I'm sure that there are other variables that may require some adjustments, but this should give you a good starting point to work with.

 

Hope this helps!



Jimmy
Teknion Data Solutions

Highlighted
Pulsar

Hi @DomMiro 

I also started down the generate dates and find the unmatched, but that wasn't getting the result.

 

You also have to account for overlapping dates, and dates before the booked time starts.

 

The attached workflow is a bit convoluted, but gets the job done.

 

image.png

 

Let me know if this helps. I'll noodle on it some more and see if I can solve it more elegantly.

 

Cheers!

Esther

Atom

Thanks everyone for your help and sorry for the late reply. EstherB47 workflow has given me exactly what I needed. The other two were close and they have me ideas. Thanks again!

Labels