Challenge #234: Spell It Out For Me - Part 1

A solution to last week’s challenge can be found here. Source: https://www.mashupmath.com/blog/expanded-form-with-decimals

This week's challenge is a two parter that will include a follow up challenge next week. For now, we dig into part 1!

If you've ever worked with continuous variables in Designer, you may have noticed that the values are left justified in the Results Window. This can make it a little more difficult to read your dataset. Convert the values provided into a table that lines up the decimals and has only one character per cell.

challenge_234_start_file.yxmd

challenge_234_solution.yxmd

Parse

Preparation

Intermediate

Data Preparation

Core

Transform

Accepted answers

All comments

Aaron_Harter

Looking forward to Part 2 next week!

Spoiler

(edit 9/21 9:35 CT) video of solution overview:

Spoiler

challenge_234_AB Data_Aaron.yxmd

atcodedog05

Done and solved. Fun challenge : )

Spoiler

Cheers and Happy Analyzing : )

challenge_234_atcodedog05_solution.yxmd

Blake

I was able to make my solution dynamic so it can take as large of a number as needed but will only handle 2 decimal places for now. Maybe I'll be able to beef it up for next week!

Spoiler

challenge_234_start_fileBH.yxmd

etzele

So do I!

Spoiler

challenge_234_solution.yxmd

jeng

Spoiler

Time for regex!

challenge_234_jeng.yxmd

hlmess

I was able to make mine dynamic for the number of digits to the left and right of the decimal but I'm 100% sure there's a simpler way to do it.

Spoiler

challenge_234_hayden.yxmd

paulfound

Spoiler

Now I have to wait until next week ???? :,(

challenge_234_PF.yxmd

RolandSchubert

Interesting challenge. I tried to make it flexible by identifying the number of pre-decimals and decimals in a first step and filling/splitting the result in a second step.

Spoiler

challenge_234_solution_rsc.yxzp

JustinBabbitt

I'm looking forward to seeing how other solve this!

Spoiler

challenge_234_Complete.yxmd

Kenda

Spoiler

challenge_234_start_file.yxmd

Spalders

Spoiler

challenge_234_ADS.yxmd

mgregory1109

Good Practice Scenario

Tony_Castillo

Spoiler

challenge_234_solution_TC.yxmd

TonyA

Here's my solution. Not that different from a few of the others.

Spoiler

challenge_234_TonyA.yxmd

cblazer

Bring on Part 2!!

Spoiler

challenge_234_start_file_CharlieDPU.yxmd

Sashikumar

Thumbs UP .

Spoiler

challenge_234_start_file_sk.yxmd

mateuslucasxcrsilvei

Great challenge!

LHolmes

Fun challenge. Although my answer isn't pretty, I do see some merit in being able to turn it into an app based on the number of places to the right of the decimal.

Spoiler

challenge_234_solution_lmh.yxmd

philipmannering

My solution attached.

Spoiler

Challenge 234 - Spell it out for me - Part 1.yxmd

T_Willins

Went with a fully dynamic solution to handle any number before and after the decimal place.

Spoiler

challenge_234_solution.yxmd

DeanWest

Fun challenge! Here's my attempt to make it dynamic.

Spoiler

challenge_234_solution-DeanWest.yxmd

abrouwer

Solution Attached

challenge_234_soluition_file - AJB.yxmd

patrick_digan

Spoiler

challenge_234_start_file.yxmd

mustel

Completed using a single tool

Spoiler

and a select to remove the original amount to match the output file!

challenge_234_start_file.yxmd

Jean-Balteryx

Here is my solution !

challenge_234_workflow.yxmd

balajilolla

Spoiler

Solution Attached

challenge_234_start_file.yxmd

Awesome!! Good One

Spoiler

challenge_234_solution_mbogusz.yxzp

DataNexus

My solution. Great one to do. Haven't done RegEx in Alteryx yet.

Spoiler

challenge_234_Joel_file.yxmd

AntonioNeri

Spoiler

My approach was to first sort the amounts in descending order to get a quick view of the largest and smallest numbers and if there was any negative figure among the records. Then I used the Select tool to transform the Amount field into a string so I could alter it later with some string formulas. I tried to parse the Amounts into their whole and fractional parts using the decimal point right away, but this proved to be problematic since there were two records showing only integers -with no decimal fraction and therefore without a decimal point. The output for such records was "Null". Given this situation, I opted to use a conditional formula to add ".00" to any record not containing a decimal point.
After, I used another Formula tool to create a pair of fields for the length of the whole and the fraction part of the amounts. With the help of a Summarize tool, I could determine the maximum lengths of each of those fields in order to plan enough columns for the final output. Of course, this might be redundant given the fact that a final version of the output is provided within the challenge file and that the whole set consists of only 50 lines. But in case the result is not known a priori and for larger data sets, this information could be quite useful.
Then, I used another couple of formulas within the same tool to fill out the missing 0's to the left of the whole and to the right of the fractional amounts so all of them were the same length. One additional formula to get a column with the decimal point.
Thanks to a couple of RegEx tool I split the digits of the whole and fraction fields.
Finally, I used a Select tool to keep and rearrange the relevant fields and to set their length to 1 character. With a Sort tool, I reset the records to their original order based on their ID.

challenge_234_start_file_AntonioNeri.yxmd

AntonioNeri

Quite elegant solution, remarkable. Thaks for showing the process of your solution in such a clear way.

Kudos!

Jean-Luc

Spoiler

Nice Solution! I tried to do the same dynamic append method but hard coded the Regex Token to 12 columns. I didn't think about splitting to rows and then using cross tab - good idea!