General Discussions
Discuss any topics that are not product-specific here.
Day 3 done! Join and Summarize galore today. Didn't expect the co-ordinate/grid stuff to pop up already but fun nevertheless!
:-) now we're warming up - brings back memories of all the matrix /grid problems from prev years!
Got to use the new formula tool that does a product! Thanks for pointing this out @NicoleJ
Spoiler
Part 1:
Prep & Cleanup:
- Break down the raw data into rows and columns
- Tag which ones are numbers vs. which ones are symbols
- For the numbers - use a multi-row tool to get all contiguous numbers
The actual problem:
- For each symbol - work out each surrounding cell (using a cartesian join)
- If any of these surrounding cells match a location with a number in - then you have a match
Break into cells
Parse and clean numbers and symbols
Add all adjacents
Part 2:
Same as part 1 really - but just requires you to check for 2 matches rather than just 1.
Prep & Cleanup:
- Break down the raw data into rows and columns
- Tag which ones are numbers vs. which ones are symbols
- For the numbers - use a multi-row tool to get all contiguous numbers
The actual problem:
- For each symbol - work out each surrounding cell (using a cartesian join)
- If any of these surrounding cells match a location with a number in - then you have a match
Break into cellsParse and clean numbers and symbolsAdd all adjacentsPart 2:
Same as part 1 really - but just requires you to check for 2 matches rather than just 1.
Spoiler
Made some effort to comment what I am doing here. Here are some of my tricks which may have helped
1) I used to calculate the grid positions using a multirow formula, but I think I learnt a few years ago from @Aguisande to use the tile tool as with unique values on the recordID and this would create tile_number_sequence which essentially did the same thing, but avoids the risk of a typo in the multirow.
2) Once I got the X and Y position, I concatenated these together but padded them out so coordinate 1,3 becomes '001003' this helps with joining later on. I'm sure you could concat in short form but I felt this was too risky / the padding might help later on
3) Creating the 8 positions around the point using the formula tool and then transposed into long and thin data structure (however I did a typo in this stage which didn't affect the test file so I scratched my head why my original part 1 answer was incorrect).
4) Luckily all of the values were on the horizontal, so because I filtered out the '.' a multi-row formula was able to easily group the values together to get the value.
5) In part B, filtered out the * and used the formula to get the 8 positions around this to find if there were any values from Part 1 adjacent then focused only on those where there were 2.
Made some effort to comment what I am doing here. Here are some of my tricks which may have helped1) I used to calculate the grid positions using a multirow formula, but I think I learnt a few years ago from @Aguisande to use the tile tool as with unique values on the recordID and this would create tile_number_sequence which essentially did the same thing, but avoids the risk of a typo in the multirow.
2) Once I got the X and Y position, I concatenated these together but padded them out so coordinate 1,3 becomes '001003' this helps with joining later on. I'm sure you could concat in short form but I felt this was too risky / the padding might help later on
3) Creating the 8 positions around the point using the formula tool and then transposed into long and thin data structure (however I did a typo in this stage which didn't affect the test file so I scratched my head why my original part 1 answer was incorrect).
4) Luckily all of the values were on the horizontal, so because I filtered out the '.' a multi-row formula was able to easily group the values together to get the value.
5) In part B, filtered out the * and used the formula to get the 8 positions around this to find if there were any values from Part 1 adjacent then focused only on those where there were 2.
I don't think I've done anything special here.
Spoiler
I parsed everything out into a single row for each character and gave it a Record ID and column ID.
I split the stream into numbers and symbols, then generated the rows and appended to essentially create a 3x3 grid. I then denoted what the "cells" were in association to each symbol, and then joined those cells to the main stream. This created the "cells" to which each symbol was linked.
At this point, I solved Part 1 by cross-tabbing and concatenating the individual numbers into a cell, creating the full number and summed the value.
Part 2 was achieved by isolating the "*", grouping to find which were associated to exactly two number groups (the groups were used when the numbers were split to rows), then it was another round of cross-tabbing to get the numbers into two columns on which I could apply the calculations. Yes, I'm aware I could have done it in fewer tools if I upgraded and had the Product function...)
I parsed everything out into a single row for each character and gave it a Record ID and column ID.
I split the stream into numbers and symbols, then generated the rows and appended to essentially create a 3x3 grid. I then denoted what the "cells" were in association to each symbol, and then joined those cells to the main stream. This created the "cells" to which each symbol was linked.
At this point, I solved Part 1 by cross-tabbing and concatenating the individual numbers into a cell, creating the full number and summed the value.
Part 2 was achieved by isolating the "*", grouping to find which were associated to exactly two number groups (the groups were used when the numbers were split to rows), then it was another round of cross-tabbing to get the numbers into two columns on which I could apply the calculations. Yes, I'm aware I could have done it in fewer tools if I upgraded and had the Product function...)
Easier than part two of day one!
M.
Spoiler
I used vectors to map a 3x3 grid around my symbol to then understand if I was intersecting a number or not.
By creating the co-ordinates of the symbol against the 9 possible intersections, this enabled me to easily match and group for part 2.
It took me a while to get my workflow into something that was readable, I had far too many tools and offshoots as my example to input didn't work as expected again. Got there in the end though 😁 I hate those spanners!
I used vectors to map a 3x3 grid around my symbol to then understand if I was intersecting a number or not.
By creating the co-ordinates of the symbol against the 9 possible intersections, this enabled me to easily match and group for part 2.
It took me a while to get my workflow into something that was readable, I had far too many tools and offshoots as my example to input didn't work as expected again. Got there in the end though 😁 I hate those spanners!
I hate it when you get the example right almost immediately but spend ages working out why the main input doesn't work! That was this day for me. However another one completed with Base A. Whoop!
