This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Sorry this one took me a while, friends. I had challenges wrapping my head around what I needed. No, not exactly true. In my head, what I needed to do made sense, I just had a challenge with the execution thereof. Lots of twists and turns, frustrations, and finally an answer that worked!
This one reminded me of one of the earlier challenges from last year, where you basically picked off nodes from a list as you went through the relationships. So, tried that approach and it failed. This isn't the same challenge at all, rather we're building branches. First attempt is a no-go.
Second attempt. Let's try to build the individual branches. A simple iterative macro SHOULD do the trick, where you join data back to itself, and use a formula tool to concatenate the strings together to make the branch. Something like this:
Well that's great, and it almost builds each string. Problem is, that doesn't include all of the "steps", it's only the longest path for each branch. Second attempt fails.
Next approach. Take the macro and modify it to have an output at each iteration instead of outputting only what doesn't have a match in the join. Getting much closer here. The output labeled "2" should give the pieces of each step along the way. So, from A-D, we would get A-B, A-B-C, and A-B-C-D
Getting so much closer, but still not there yet. Time to work outside the macro. Not all of the paths are fully built for the "2" output. I could probably fix that, but by now I want to finish and not necessarily in the most efficient and elegant way. So, a formula tool adds on the last piece to each "branch", then transpose out and remove any duplicates (another failed attempt resulted from not noticing duplicates. Ugh!!). Finally, a fun use of Regex_CountMatches to count how many objects are in each orbit. Subtract one because we don't count the home planet.
Finally successful with part 1. Part 2 involved finding a path from "YOU" to "SANTA". I'm hoping that after all of the time spent building each path, I can find the length between the two easily, and I can. See which paths end with "SAN" and "YOU", and then count the number of objects that don't match.
Overall, much more challenging than it should have been. Travel, herding children (at this point that may be as fruitful as herding cats) in a foreign country where we thankfully speak the language, spotty WiFi may be conspiring against success here. At least my ultimate solution is pretty fast in the end.
OK, this one was (finally!) pretty easy for me, since I work with HR data in my day job (traversing hierarchies is a common task).
For part 1, I used a super-simple iterative macro that essentially works inward from each satellite. For example, using the simple input from the problem:
L is a satellite of K
K is a satellite of J
L is a satellite of J (because L is a satellite of K, and K is a satellite of J)
K is a satellite of E (because K is a satellite of J, and J is a satellite of E)
Then, after getting all of the direct and indirect orbits, just add them up with a Count Rows tool. To me, the problem sounded just like calculating All Reports in an HR hierarchy, where we count up a person's direct and indirect reports (I am a direct report to my supervisor, but I'm also an indirect report to his supervisor, and an indirect report to her supervisor... all the way up to the CEO).
For part 2, my iterative macro searches inward and outward from the input (YOU), building the path until it hits SAN. Then, just count delimiters (I used a pipe). Had I known what part two would be, I would have gone ahead and output the full paths in part 1 like @EstherB47 did! Alternatively, I could have joined/concatenated the output from part 1.
In my solution on github, I have a stray summarize tool at the end of part 2 (no idea why I added that - it's completely unnecessary, since only one record is output from the macro!)