Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #183: Roman Numeral Math

arjanloogman
8 - Asteroid

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

Flo_G
7 - Meteor

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!

dhtay
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.

OllieClarke
15 - Aurora
15 - Aurora

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

NicholasM
Alteryx Alumni (Retired)
Spoiler
Capture.PNG
NicoleJohnson
ACE Emeritus
ACE Emeritus

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 

sgrabish1
8 - Asteroid

Thanks for practice exercise!

avilagon
7 - Meteor
 
LiD
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!

tiffany_chen
8 - Asteroid

Interesting use of Dynamic Replace!

Spoiler
2019-11-18_143135.png