Weekly Challenge

KOBoyle · ‎11-19-2018

Solution attached.

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.

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 until I was done.

patrick_digan · ‎11-19-2018

Spoiler

Dynamic Replace is the best kept secret!

EstherB47 · ‎11-19-2018

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

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 sampleMy 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

Joe_Mako · ‎11-19-2018

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.

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: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() ENDIFThis 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.