Discussion thread for day 5 of the Advent of Code - https://adventofcode.com/2022/day/5
Definitely not my most elegant solution yet. Will come back to try without an iterative macro.
Macro
Hi @bflick,
Definitely looking forward to the one without the iterative macro 😀
I won't play Tool Golf today ...
Part 1 Macro:
Part 2 Macro:
I have a tendency to build metacode solutions: using Alteryx to dynamically assemble expressions/queries/workflows for me. If there were an eval() function, this nested macro could have been some kind of multi-row formula...
Outer iterative macro:
Inner batch macro:
Decided last night that I'd set a 4:50am alarm to get up and give this a go as soon as it drops... Certainly didn't expect to be nesting a batch macro inside an iterative macro when I was jolted out of bed and I think it's a sign that nobody should ever get up early for anything.
Iterative macro:
Batch macro:
My solution.
I used Iterative macros. Suddenly the difficulty level increased.
Decided early that this needed an iterative macro. Changed my mind and tried to make a batch macro work. It took me an embarrassing amount of time to figure out that no... batch macros would absolutely not work here (at least in my implementation). I suppose this is my annual reminder to brush up on iterative macros!
My final solution is over engineered, but it works and I'll take that as a win today! ">⭐">⭐
I was also pretty hyped that part 2 was as simple as switching a tool sort for me.
Day 5 done ! ✔️
It begun, ... the iterative challenges ! This is how they all started to lose faith ...
Day 5 is definitely a step up, marking the first time Iterative macro is called on to do almost code-like tasks in BaseA.
If only Alteryx has the Matrix muscles of Matlab!
Generating the clean arrays take quite a few tools...
... thus making the Macros relatively mild. Only 1 tool difference between part 1 and part 2 for me.
I can predict that 6 will be worse...
Not pretty for me today, but I did get there eventually. Part2 was easy once I got Part1
Macro:
@clmc9601 wrote: I have a tendency to build metacode solutions: using Alteryx to dynamically assemble expressions/queries/workflows for me. If there were an eval() function, this nested macro could have been some kind of multi-row formula...
@clmc9601 I'd love to see an eval function! It could be really powerful.
I saved all the tough iterative macro weekly challenges to the end and just did them so I think I was in the right mindset for this one. Big step up but a lot of fun (would be a few extra tools at the start and another macro input to be a perfect input to output so not fully applicable to golf)
Here is mine for today. A perfect use case for the CReW Dynamic Formula tool!
getting the inputs in the right format was by far the most annoying bit
I built star 1 and 2 with two different macros that are almost exactly the same so I've only posted one Workflow then Macro
Iterative macro that I spent too much time trying to organize. I had four outputs: 1 for loop, 1 for done, 1 for every iteration and 1 for every iteration as a matrix to spot check. Had to manually create a table from the input. P2 just was a sort switch from P1
Solution with no macro for part 1 :
The monster formula that allows it (and has driven me insane) :
IIF([Origin] = 1, IIF(Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]) = "", ".", Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number])) + Substring(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), FindString(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), " ")), REGEX_Replace(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), "((.*? ){"+ToString([Origin]-1)+"})[^ ]*(.*)", "\1"+IIF(Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]) = "", ".", Right(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1), Length(GetWord(IIF([Destination] = 1, Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".") + Substring([Row-1:Crates], FindString([Row-1:Crates], " ")), REGEX_Replace([Row-1:Crates], "((.*? ){"+ToString([Destination]-1)+"})[^ ]*(.*)", "\1"+Trim(ReverseString(Left(GetWord([Row-1:Crates], [Origin] - 1), [Number])) + GetWord([Row-1:Crates], [Destination] - 1), ".")+"\3")), [Origin] - 1)) - [Number]))+"\3"))
Part 2 is achieved by removing the ReverseString function.
As part 2 takes change of only one tool, guess I am on the right track, a bit long of flipping the rows 😅
Took me hours to find one tiny bug in my iterative macro. Only got a sniff of what was going on when I started counting the overall number of crates (56) at every iteration and realized that crates were mysteriously going missing... The crate thief turned out to be the REPLACE function!!! I promptly replaced it with a SubString function.
A bit late to the party! Did this one with string functions 😀
Dang! This got much harder.
I spent entirely too long trying to build this out in a matrix style solution only to revert back to stack strings.
And had to refer to the interactive lessons to setup the iterative macro.
Also went for the string functions and reverse string approach. Used a batch macro to dynamically send in the move length and from and to fields to the crate moving process, the output of which had to be iterated upon, so batch within an iterative. Tons of troubleshooting necessary, lost over half my crates at one point and have lost more than half of my marbles by attempting this before work to get no stars, then an hour or two again after work for two very frustrating/satisfying stars.
Went the non-macro route. 18 tools for both solutions. Muti-Row and creative use of the Replace function FTW. I'm sure there is a more elegant way to write this formula, but it works!
IF [RecordID]=10 THEN Replace(replace([Combined],getword([Combined],[To]-1),(left(GetWord([Combined], [From]-1),[Number]))+getword([Combined],[To]-1)),GetWord([Combined],[From]-1),right(GetWord([Combined],[From]-1),length(GetWord([Combined],[From]-1))-[Number]))ELSE
Replace(replace([Row-1:Combined2],getword([Row-1:Combined2],[To]-1),(left(GetWord([Row-1:Combined2], [From]-1),[Number]))+getword([Row-1:Combined2],[To]-1)),GetWord([Row-1:Combined2],[From]-1),right(GetWord([Row-1:Combined2],[From]-1),length(GetWord([Row-1:Combined2],[From]-1))-[Number]))ENDIF
Well this one was... gross. But after solving it a gross way last night (so gross, in fact, that I will not be sharing that one to the community at large, for vanity reasons), and then letting it haunt me all day today... I finally figured out how to Tool Golf this one! 18 tools for both parts!! All that's needed is to change a True to a False and BAM! Two solutions. 😁
Solved again using Python #SnakingMyWayThroughChallenges
My solution with and iterative macro
remember to reverse string if you use transpose.
add check to pick whether want to use reversestring()
I did this in 2 ways - one with an iterative macro that includes formulae; and one with an iterative macro that includes dynamic replace (thank you Digan). I like the Dynamic Replace one a lot because it's one of the only easy-ish ways to do dynamic code execution in Alteryx (other than Chris's Dynamic creation of workflow XML)
Absolutely needs to be refactored - especially in the light of other people doing this without macros.
Also absolutely not gonna win any tool golf here...
The parser macro:
The Dynamic Replace Macro:
the formula version of the macro
10 tools, most of it parsing the data. I managed to do the stack moving with some hefty multi-row string calcs.
Sharing my No Macro approach for Day 5
OMG, that formula ... lol,
ps sent you a note to connect on LinkedIn
That's clever. I started down a similar to start, but couldn't make it work. Well done.
Impressive! Do you mind sharing a yxzp of this? I would love to dive into the details to see how you made this work.
