community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.

Challenge #142: Life Certainties - Workflows, Death, and Taxes

Alteryx Certified Partner

Solution attached.

 

Spoiler
I examined the Transform and Developer tools looking for a way to come up with a reasonably streamlined solution, and realized the capabilities of the Arrange tool. I didn't see the comment from @JoeM until I was done.


challenge_142_spoiler.png
Spoiler
Dynamic Replace is the best kept secret!

Capture.PNG
Bolide

Unexpected benefit of jury duty is having the time to catch up on challenges. My first go at both a hard and easy solution are very similar. So I decided to try another approach. Dynamic Replace, I'm beginning to understand you......

Also, I love RegEx

Spoiler
First solution was a "hard" one. Used Arrange instead of transpose, append fields, filter and summarize.

image.png

"Easy" path wasn't my natural approach excepting the Transpose tool. Wanted to do something other than summarize, so chose to sort and sample
image.png
My favorite solution was third. Using Dynamic Replace, but using the Summary output to see how many tax rates were used. This uses the fewest tools, but the quote marks to use in the condition expression took a lot of trial and error
image.png
Quasar
Quasar

The Arrange tool needs some love, a powerful data transformation tool, but this week's challenge does take advantage of all its capabilities.

 

Spoiler
142.png

The Arrange tool can do the exact same thing as a Transpose tool, and it can do more, but the interface is not intuitive. While the Transpose tool just stacks into a single column, the Arrange tool can stack into multiple columns, and adds an additional custom text column.

To replicate the Transpose tool, you can use this setting when adding your first column, notice that is is set to use the field names for the text of the added description column:
add column.png

Here is the formula I used to match the values and type:
If EndsWith([Salary], Substring([Name],7,1))
AND ToNumber([Salary],1,1)<[Value]
THEN [Rate]
ELSE Null() ENDIF
This takes advantage of how the ToNumber() function works, it will convert all numbers prior to the first non-numeric character. We can suppress the conversion warning with the additional arguments.

We can also check the last character matches with the EndsWith() function.

The list of company names was not needed to compute the final result.
Alteryx Partner
Spoiler
Capture.PNG

An easy way version for now. 

Spoiler
I took the easy solution.... 
Screen Shot 2018-11-20 at 10.27.43.png

 

 

Alteryx Certified Partner

Done :-)

 

Spoiler
With a batch macro :-)

1.JPG


2.JPG
Alteryx Certified Partner

Done :-)

 

Spoiler
With a batch macro :-)

1.JPG


2.JPG
Alteryx
Alteryx

I opted out of the hard way, but maybe I'll come back to it when I have some more time on my hands! 

 

Easy way for now :)

Spoiler
 2018-11-19 03_28_36-Window.png
Asteroid

I went easy on this one as struggling for time. I could have removed a tool or two but just had to do this quick!

 

Spoiler
142.PNG