General Discussions
Discuss any topics that are not product-specific here.
It's time to use Macro but not Iterative one yet.
Spoiler
Part1: Put flag as per this expression, and concat flag for each report.
We need to know the number of records which is '+++++' or '-----' so apply Regex_Match() function, and count the True record.
*we need to escape meta character like '+' by '\'
Part2: Basically the same logic as Part1 was applied. Difficult point was how to prepare all pattern that miss just one level.
I go with Batch Macro; 1st batch=remove 1st level, 2nd batch=remove 2 level....
Of course, the outcome contains duplicated Report so count distinct by Summarize tool.
We need to know the number of records which is '+++++' or '-----' so apply Regex_Match() function, and count the True record.
*we need to escape meta character like '+' by '\'
Part2: Basically the same logic as Part1 was applied. Difficult point was how to prepare all pattern that miss just one level.
I go with Batch Macro; 1st batch=remove 1st level, 2nd batch=remove 2 level....
Of course, the outcome contains duplicated Report so count distinct by Summarize tool.
Updated x2 (Tool Golf/Optimized version V4):
Spoiler
To accomplish a 6 Tool solution, the Generate Rows Tool is quite the monster, utilizing a 2D matrix loop to accomplish what the Text to Columns/RegEx Tool would have. Not quite as elegant, but a stroke is a stroke. A very elegant 7 tool solution can be achieved with very reasonable formulae.
To accomplish a 6 Tool solution, the Generate Rows Tool is quite the monster, utilizing a 2D matrix loop to accomplish what the Text to Columns/RegEx Tool would have. Not quite as elegant, but a stroke is a stroke. A very elegant 7 tool solution can be achieved with very reasonable formulae.
Updated (Tool Golf/Optimized version V3 - elegant)
Spoiler
I am quite pleased with the elegance and simplicity of this solution.
I am quite pleased with the elegance and simplicity of this solution.
Updated (Tool Golf/Optimized version V2):
Spoiler
Achieves both Part 1 and Part 2 using 9 tools (per part). Multi Row Formula Tool requires some extra logic to make this work:
Achieves both Part 1 and Part 2 using 9 tools (per part). Multi Row Formula Tool requires some extra logic to make this work:
(My Original Solution) No fancy macros from me yet:
Spoiler
For part 2, I joined the output of part 1 with main data to identify "Unsafe" reports that could benefit from the dampener, then I applied the exact same logic as Part 1 after dampening (Generate rows to brute force)
For part 2, I joined the output of part 1 with main data to identify "Unsafe" reports that could benefit from the dampener, then I applied the exact same logic as Part 1 after dampening (Generate rows to brute force)
Day 2 was a nice case of ugly solutions that work. Considered a batch macro but chose to brute force it rather than fiddle with inputs.
The trick is
Spoiler
Creating all of the possible iterations of set - 1 row. You can do this for the maximum length set (8 in my case) since you're going to have to sum away duplicates anyway.
My version had a small hiccup in that I was throwing out row 1 (which is always safe but has an extra case on the change calculation.
In Part 2 that is only right if you aren't working on the iteration that doesn't have row 1, so you need to do Min(Row) instead of Row1.
My version had a small hiccup in that I was throwing out row 1 (which is always safe but has an extra case on the change calculation.
In Part 2 that is only right if you aren't working on the iteration that doesn't have row 1, so you need to do Min(Row) instead of Row1.
This one definitely challenged me more than I expected for Day 2. The concepts felt straightforward, but my execution was... not. I found myself reworking the logic several times. I have the feeling there's a more straightforward approach, but my tired brain couldn't find it today. Hoping to revisit this one!
Spoiler
One of my gotcha's was the way I handled the change in value for the first value in the multi-row formula. I also mishandled situations where the change was neither an increase or a decrease. Lots of little pieces of logic to think through in this one!
One of my gotcha's was the way I handled the change in value for the first value in the multi-row formula. I also mishandled situations where the change was neither an increase or a decrease. Lots of little pieces of logic to think through in this one!
