community
cancel
Showing results for 
Search instead for 
Did you mean: 
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Weekly Challenge
Do you have the skills to make it to the top? Subscribe to our weekly challenges. Try your best to solve the problem, share your solution, and see how others tackled the same problem. We share our answer too.
Unable to display your progress at this time. Please try again a little later, or contact an administrator if you continue to see this error.
Getting started with Designer? | Start your journey with our new Learning Path!

Challenge #183: Roman Numeral Math

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

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!

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.

Alteryx 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

Alteryx
Alteryx
Spoiler
Capture.PNG
Magnetar
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 

Asteroid

Thanks for practice exercise!

Atom
 
Meteor
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!

Meteoroid

Interesting use of Dynamic Replace!

Spoiler
2019-11-18_143135.png