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

Last week's solution can be found here!

This week's challenge is around assigning the right income tax rate to each salary. Your goal is to count the number of people associated with each tax rate.

How many feel at tax time.

In our starting workflow, you'll find three datasets:
1) A grid of 100 employees from 26 companies. Before everyone gets worried, yes, I randomly generate this salary data and it is not representative of the population! Do note that each salary has a 'I' or 'J" representing whether this salary will be filed jointly or individually

2) A list of the company names
3) A tax table with the income rates. Notice that there is not a range in the tax table. You must infer the range based on when the next tax rate starts.

Easy Path:
1) Solve this problem as is.

Hard Path:
1) Do not use: Join, Crosstab, Transpose, Multi-field Formula, or Multi-Row Formula

challenge_142_start_file.yxmd

challenge_142__solution.yxzp

Developer

Join

Preparation

Advanced

Data Preparation

Transform

Basic

Accepted answers

All comments

NicoleJohnson

Why take the easy path when you can do it the hard way? Which, coincidentally, is generally how I approach my taxes.

My solution! Complete with super cute piggy bank macro icon!

Spoiler

Chose to go the batch macro route since all of my favorite pivoting tools were on the do-not-use list... It actually ended up bring a fairly simple solution! Append + Filter achieved the "join" to the tax data, and some Dynamic Select trickery with the batch macro allowed me to calculate data for all company columns without an excessive number of formulas.

This little piggy paid his taxes... and cried wee wee wee all the way home.

Super fun challenge, @JoeM!! I really enjoyed this Monday morning brain teaser

Cheers!

challenge_142_NicoleJohnson.yxzp

nikolaskardasis

Hi all,

My (not at all efficient) solution but still within the rules!

Spoiler

challenge_142_solution.yxmd

cgoodman

Two macro solution. It's fun trying to find a harder solution (which is actually better than what I was trying to do the easy way), to make you think outside the box every now and then.

Spoiler

challenge_142_solution.yxzp

terry10

I like regex!

Spoiler

challenge_142_terry10.yxmd

JoBen

Cheers! I went with the hard path, and here is what I came up with...

Spoiler

I decided to use an iterative macro to stack the company columns and then I brought it all back into the workflow.

My Iterative Macro

My Workflow

challenge_142_Joben_Solution.yxzp

WilliamR

Using the second path and discovering a new tool.

challenge_142_WilliamR.yxmd

ggruccio

Took the hard way!

challenge_142_gg_finish.yxmd

JoeM

@WilliamR, I put all the restrictions to see if I could get people to discover a tool that would make their life easier. It was not Arrange that I was aiming to have people discover, but when I was thinking about disallowing it for the 'hard' way, I decided it was also a handy tool to have exposure to. I just didn't think anyone would do it (except for the one and only @Joe_Mako of course)!

DataPrepChad

Not sure I'd call this the "easy" way, but great challenge!

Spoiler

challenge_142_DataPrepU.yxmd

Ladarthure

Hi,

funny one, wanted to go the easy way first but ended up doing it the hard way

challenge_142_start_file.yxmd

kat

Looks like @NicoleJohnson and I had very similar thinking here. Eliminating certain tools gives things an interesting spin..

Spoiler

batch macro to the rescue! Challenge #142.PNG

Challenge #142.yxzp

DataPrepChad

@ggruccio I like what you did with the concat, gives me an idea!

SamDesk

Just the easy way for now

Spoiler

challenge_142_start_file.yxmd

GarthMiles

Spoiler

Screen Shot 2018-11-19 at 3.24.37 PM.png

Given the "Hard Way" constraint, Dynamic Replace is arguably the best straight-line approach here. However, if comprehension were a measure, Dynamic Replace is second to none in complicating the simple.

challenge_142_solution_file.yxmd

DataPrepChad

@JoeM I think I know where you were going with introducing a new tool. I couldn't help myself, Dynamic Replace to the rescue.

Spoiler

challenge_142_DataPrepU.yxmd

KOBoyle

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_start_file_KO.yxmd

patrick_digan

Spoiler

Dynamic Replace is the best kept secret!

challenge_142_start_file.yxmd

estherb47

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.

"Easy" path wasn't my natural approach excepting the Transpose tool. Wanted to do something other than summarize, so chose to sort and sample

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

challenge_142_EHB_solution.yxmd

Joe_Mako

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

Spoiler

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.

challenge_142_Joe_Mako.yxmd

George_Walker

Spoiler

An easy way version for now.

challenge_142_solution_file.yxmd

NikolajFrosig

Spoiler

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

challenge_142_start_file.yxmd

olivia_paquot

Done :-)

Spoiler

With a batch macro :-)

olivia_paquot

Done :-)

Spoiler

With a batch macro :-)

challenge_142_start_file.yxmd

vsoni

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

challenge_142_completed.yxmd

clant

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

challenge_142_Fin_CL_file.yxmd

sprodger

Spoiler

my solution, like others, decided to use the arrange tool . probably could have used a few less tool overall though.

challenge_142_solution_SP.yxmd

SrideviB

started looking in to Alteryx only a few weeks back and my 1st ever workflow challenge.. Thanks for keeping everything so organized in the community.. from learning material, challenges and training. Thanks, Sri

challenge_142_start_file_SrideviB.yxmd

JoeS

I used the arrange tool too. In 10 years of using Alteryx it's the first time I have used it to solve something, previously I have only used it to see what it did.

Managed to get the solution down to 4 tools as well, which I was pretty happy with!

Spoiler

challenge_142_solution.yxmd

wenjuanchen

@NicoleJohnson Love your super cute icon, and your icons for other macros!

wenjuanchen

Solution attached. Had fun solving it. Trying to put a better icon for the macro this time:)

Thanks @JoeM for sharing this challenge!

Spoiler

Spoiler

challenge_142_solution_wchen.yxzp

OliverW

As a Solutions Engineer who likes to think of easiest solutions, no Macro, no hard path, just simple Alteryx functions (and a little RegEx for some extra spin.

Spoiler

challenge_142_end_file.yxmd

KevinW

Went with the easy path as I wasn't trying for the style points this morning - just get 'r done.

Spoiler

challenge_142_solution.yxmd

Pollution

Not the most elegant solution but it'll work

Spoiler

challenge_142Pollution.yxmd

Carter_McGary

No need to even touch the 'companies' input

Weekly Challenge 142_Salary and Tax Rate.yxmd

I chose the easy path! My solution:

Spoiler

challenge_142_EW.yxmd

jssandom

I took the easy route!

challenge_142_solution_JSS.yxmd

Erin

I peeked at some of the posted solutions under the Hard route method and you guys are too fancy!

I am a beginner and so I took the easy route today! 😎 This solution doesn't use Macros or complex formulas - and mostly all tools for beginners, except for just a couple....

Spoiler

Only somewhat tricky tools I used is a RegEx and a MultiRow formula tool:

challenge_142_submission_EM.yxmd

derekbelyea

Spoiler

challenge_142_income tax rates.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703