General Discussions
Discuss any topics that are not product-specific here.
Today was a good confidence booster! I started by trying to avoid an iterative macro, but found the logic was more straightforward with a macro than without (at least to me). Now I want to go back and try a non-macro solution!
Part 1:
The bulk of this logic was done in a simple multi-row formula tool with built-in Alteryx functions, which just felt so good. CharToInt() was so useful.
Part 2:
After rereading the instructions approximately 15 times, I finally felt like I understood the problem. It luckily sounded much more complicated than it ended up being in practice.
- Re-apply the "HASHMAP" algorithm to just the lens label instead of the full string
- Find the first line with a = operation and remove any records before that. My production data wasn't working in my iterative macro because the first 3 operations were removals, so this was my solve for that.
- Find max iterations and use that to stop the macro
- Use iterative macro to see if a label already existed in a box, and apply the operation. The workhorse of this macro is the join tool between the box map and the instruction set.
Workflow:
Macro:
This was a fun one, once I figured out the instructions (which took me longer than I'd like to admit...). The method here is pretty much just brute force, so I won't share any further details as to algorithm. I think the workflow itself is a pretty cool use-case for creating conditional logic in Alteryx (i.e. update if row exists, add row if not)
Workflow:
Hash String (iterative macro):
Fill Boxes with Lenses (iterative macro):
I read part 2 for 20+ times. to find out how to allocate the box.
and today also a non macro day.
part2:
1. use part one method for label only (note: input more that 2 characters, waste lot of time to found this)
2. sort descending RecordID
3. take first "=" for value, and take last "=" before "-" for RecordID
4. sort RecordID back to ascending and add sequence_num (slot) by box.
5. calculate as request and sum
| recordID | label | action | value | box | type | remark |
| 10 | aa | = | 5 | 1 | take | first, for value |
| 7 | aa | = | 4 | 1 | take | last, for recordID |
| 5 | aa | - | 1 | stop | ||
| 3 | aa | = | 3 | 1 | stop | |
| 1 | aa | = | 2 | 1 | stop |
Part 2 the trick is to group the values by the boxes and then you just need to work out if the operation is to add a new lens, replace an existing lens or take a lens out. So you can build out a string with the lens in order and make use of regex_replace functions to either replace or delete.
IF !Contains([Row-1:lensBox],[label]) AND [Operation] = "=" THEN
[Row-1:lensBox]+"["+[label]+" "+tostring([FocalLength])+"]"
ELSEIF
Contains([Row-1:lensBox],[label]) AND [Operation] = "="
THEN
REGEX_Replace([Row-1:lensBox], [label]+'(\s\d)', [label]+" "+tostring([FocalLength]))
ELSEIF
[Operation] = "-" AND contains([Row-1:lensBox],[label])
THEN
REGEX_Replace([Row-1:lensBox], '(\[)'+[label]+'(\s\d\])',"")
ELSE
[Row-1:lensBox]
ENDIF
(Ignore the lack of indentation!)
Part 1 is straightforward. But Part 2 question text was too difficult to understand. I continued to search the box number.
