Email Notification with specific table & format

[SubbuSharma]

Hello, 

I am trying to automate a very mundane work which consists of ...

1) Calculating 60 days from day 1, i.e; today, and has to be dynamic, and spit out MM/DD/YYY. Pls refer column O in tab "information"

2) Calculate duration backwards (60 - 1) and spit out business days & months only, pls refer columns E to O in tab "information"

3) Once we have above 2, update a specific table - pls see tab "Table Format with contents". Ideally, in every instance we update the critical information that is specified in column C of the table. 

4) Email the updated table to the concerned team

** Kindly note, workflow should be able to determine couple of things 1) RCI Name and 2) RCI Number to pick and spit out the details 

Is there a way to Alteryx could help us in automating the above 4 points? Currently, for each instance, we are taking 1 hour to complete and there are scenarios where we are spending 4-5 hours in a day

[frank_sanseri]

Hi, @SubbuSharma.

I wanted to tackle the date range portion of your process because it's always an interesting challenge to calculate and use business days; and you have the added challenge of creating some custom-formatted plain-text ranges, e.g., "Sep 13-Oct 18."

I had to make some assumptions based strictly on your attachment, so I created a text input which you might actually have as a flat file or database table someplace. And I pulled down a list of 2022 US holidays, since that's going to be part of the business day calculation. If you have additional company holidays, or holidays to exclude, you can edit that source file, or create your own.

The input I created for this is a single-row table that lists the start date from your doc, and a numerical start and stop business day for each milestone:

We'll use this in all the date calculations. 

From there, I generated a group of rows for all the calendar dates in the specified 60-day range, used a formula to remove the weekend days, and used the results of a left join to a list of US holidays to show only the business days.

From that point, I used a Record ID tool to number the remaining days, generating the final list of business days.  I used a transpose tool on the original one-row input table so the data could easily join on business day field, and then used a crosstab tool to turn the data set back into a wide one-row table:

Then I was ready to convert the start and stop business day dates into the ranges shown on your sample. This step was a tad repetitive, but manageable this way because there were only 9 milestones. You can see the results in the preview window:

After that, it's largely some cleanup -- removing some columns we don't need any longer -- and using the DateTime tool to create the text value for your Column O:

I don't have enough information to help with step 3, but for step 4, it sounds like you'll use the rendering tools to create your formatted output, and then an email tool to send it to the parties of your choice.  

I've attached the workflow for the portion I've described above, and hope you can use it as a launchpad for the rest of your work. I'll keep an eye out for any follow-up you post.

Good luck!

:: Frank

[SubbuSharma]

Hi @frank_sanseri 

Firstly, thank you so very much for the workflow. Need to pick your brain on few things. 

1) the Date range portion (input tool), we are trying to calculate this: for instance - 

    a) Of the 01- 43 calendar days (column A);  

                i) What is the duration (column C)

                ii) how many business day (Column C)

2) I am trying to incorporate that change in the M1Start M1Stop section however, I am hitting a roadblock. Please find the workflow attached for your reference.

3) I have changed USA calendar to Canadian calendar

i'll have to bug you more about the emailing the documents in a bit :)