Challenge #41: Analytics PayPeriodCalc

[archietao]

My solution: 

[ewelch531]

Solution Attached. Learned a lot on this one.

[nini]

My Solution:

[KeithTroutt]

My solution

[TharunReddy]

Step 1: Date Time Parse of the Start and End Dates

Step 2: Generate Intermediate Days between Start and End dates parallelly fro P1 and P2

Step 3: Get the days out of the parsed dates

Step 4: Count the weekdays present

Step 5: Join P1 and P2 to get the result

Happy practice :)

[sharathkumargurram1]

Hi ,

Finally uploading the solution had to refer alot.

Best Regards,

Sharath Kumar Gurram.

[sharathkumargurram1]

Hi ,

Uploaded the solution did a lot of research challenge seems tricky.

Best Regards,

Sharath Kumar Gurram.

[rfergus]

Spoiler

[Verakso]

First, when I saw this challenge I was like

I am on my way to the top on these weekly challenges, but I had to admit that my knowledge is not to take them on chronological, so I have started with all the ones marked as Beginner.

But when I dug into this one I was more like

I could not believe, that there was not a built in function to calculate working days between two days. That even exists in Excel!

For a brief moment, I was attempted to export the data to Excel and then import them again, but that was probably not the point of this challenge.

Generally, I can see, that this has been solved by generating a row for each day, and then check the weekday, and then filter out weekends and count the remaining days.

Well hats off, but I still think that was a lot of work for what is a quite basic calculation in many other applications.

I must give a big shout out to @Joe_Mako , who I think provided the most elegant solution to this challenge.

So with no further ado, here is my solution, and as a big spoiler alert, I over complicated this “simple” task, by doing it in Python.

Spoiler

My initial thought when I found out, that there was not a built in formula for calculating, was to make one myself. You can do that you know in Alteryx, but again – not something I have been really looking at, so for this I took a completely different approach.

Well, Alteryx does support Python, and I thought, that Python ought to do this quicker – oh boy was I wrong but it was fun.

I started out, by looking at Tool Mastery | Python pages to get a better understanding on how Python in Alteryx works.

I had been poking around in Jupyter Notebooks that Alteryx is using so that part was easy.

The next part on how to get the data in and right, parse the dates and use the numpy function to count business days.

So with the help from Google, and my son who I studying to become a Bachelor in Software Development, I did manage to narrow it down, with this Python code

[1]: First we import the data

from ayx import Alteryx

data = Alteryx.read("#1")

[2]: Then we import the needed modules, and parse and count the business days, and return the data as a new Panda DataFrame

import numpy as np
import datetime as dt
import pandas as pd

def to_date(string):
    return dt.datetime.strptime(string,"%m/%d/%Y").date()

result = pd.DataFrame(columns=["Month", "P1_Weekdays", "P2_Weekdays"])

for index, row in data.iterrows():
    row = [to_date(r) for r in row]
    p1 = np.busday_count(row[0],row[1] + dt.timedelta(days=1))
    p2 = np.busday_count(row[2],row[3] + dt.timedelta(days=1))
    result.loc[index] = [row[0].strftime("%B, %Y"), p1, p2]   

print(result)
Alteryx.write(result, 1)

There was a few thing I could not manage to do in Python

The Python component complaints if I have spaces in the column headers, so that is fixed in Alteryx with a dynamic replace.

Then there was the sort order of the Month, for some strange reason sort_value did not work (or at least not as I expected it to work)

So in the end, my Workflow ended up looking like this

Was it faster, probably not, but it was fun to take this twist on the challenge to learn some Python, and in the end I ended up with the same result.

Back on the trail to the top
/Thomas

[FeedMeData]

I needed date practice, thanks!