Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

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

KOBoyle
11 - Bolide

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
patrick_digan
17 - Castor
17 - Castor
Spoiler
Dynamic Replace is the best kept secret!

Capture.PNG
estherb47
15 - Aurora
15 - Aurora

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
Joe_Mako
12 - 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.
George_Walker
8 - Asteroid
Spoiler
Capture.PNG

An easy way version for now. 

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

 

 

olivia_paquot
8 - Asteroid

Done :-)

 

Spoiler
With a batch macro :-)

1.JPG


2.JPG
olivia_paquot
8 - Asteroid

Done :-)

 

Spoiler
With a batch macro :-)

1.JPG


2.JPG
vsoni
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
clant
8 - 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