Weekly Challenge

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

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback
We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.

Challenge #183: Roman Numeral Math

Highlighted
Alteryx Partner

My Solution...

 

It works on this particular case, don't know how my multi-row formula would perform on values like CXCIV...

 

Spoiler
Screenshot 2019-10-28 at 16.16.41.png

 

Also, did not know about the dynamic replace tool and its abilities; nice to learn about it when reading other people's solutions, used a batch macro for this myself.

 

Nice one, I liked this challenge!

 

Kind regards,

 

Arjan Loogman

Highlighted
6 - Meteoroid

This was fun!

 

Spoiler
fguardini_0-1572278665844.png


First I created an additional reference table with Roman abbreviations - this includes IX for 9, and for scrupulous I added others as well (IV, XC, CM, etc). The values are modified to have a separator before them (,). I then used find/replace on abbreviations first and then on numbers on the formula operators in column. Summarize to get the total number for each one. Replace in the original formula. I see many people used Dynamic Replace - I went ahead with a very simple R script to evaluate a string as formula (eval(parse(text=formula))). Will look into Dynamic Replace for the next time!

Highlighted
8 - Asteroid

This was a fun one! I knew there had to be an eval() function/tool somewhere, but it wasn't where I expected it to be.

Highlighted
Alteryx Certified Partner

That was great! I really enjoyed that

Spoiler
My multi-row formula is evidence of getting the wrong answer and then going back to patch it

"IF ISNULL([row-1:field2]) AND ISnull([row+1:field2]) THEN [field2]
ELSEIf ISNULL([row-1:Field2]) and [field2]>= [row+1:field2] THEN [field2]
ELSEIF ISNULL([row-1:field2]) AND [field2]<[row+1:field2] THEN -[field2]
ELSEIF [field2]<[row+1:field2] THEN [row-1:total]-[field2]
ELSE [row-1:total]+[field2] endif "

Challenge 183.png

Highlighted
Alteryx
Alteryx
Spoiler
Capture.PNG
Highlighted
14 - Magnetar
14 - Magnetar

Behold! Two Roads to Rome in one... I took the long way around the first go-around with an obnoxiously If-Statemented Multi-Row Formula... and then read a few of the previous posts and had the smack-the-forehead moment of Dynamic Formula realization... which ended up in a slightly longer but more "advanced" solution. So I included both.

 

Spoiler
Solution #1 - The Long Road to RomeWeeklyChallenge183.JPG
The first problem to solve was dealing with numbers like 9 (IX), so I created a combo-number lookup first, then looked up the remaining numbers with the original lookup list. Second problem was adding the numbers together, so I used a combination of delimiters to separate the numbers to rows, then Summarize to add them up by "grouping". Finally, for this version, I opted for a Multi-Row Formula approach to determine which operators to apply to values. Bit clunky, but it worked... so long as the equation was in the same format as default order of operations. 

Solution #2 - The Slightly Longer But More Interesting Road to Rome
WeeklyChallenge183Dynamic.JPGBorrowed a majority of the same logic from the first workflow, but used the Dynamic Formula tool (CReW macros for the WIN!) at the end to get the final calculation. I like this method because it doesn't require the Multi-Row Formula, which seemed sketchy at best in this particular scenario (more of a "broken clock being right twice a day" type scenario). 

Great reminder to check out some of those tools off in the categories to the right, not just the favorite Preparation Category tools! 

Cheers!

NJ 

Highlighted
8 - Asteroid

Thanks for practice exercise!

Highlighted
7 - Meteor
 
Highlighted
8 - Asteroid
Spoiler
LiD_0-1573683195778.png

The last step is indeed the most delicate one!

Using R may be the obvious way to evaluate the equation/formula, but definitely not prefered.

"Dynamic replace" is much better in terms of performance and appropriateness for this task. Worth learning and exercising!

Highlighted
8 - Asteroid

Interesting use of Dynamic Replace!

Spoiler
2019-11-18_143135.png