Multi Row - Multi Field

[BPetrosilli]

I've been beating my head against the wall trying to come up with an easy way to create a dynamic multi-row, multi-field macro so I think an outside perspective may be needed. 

I have the following situation.

So essentially I have a hierarchy that I need to "fill down" and then concatenate across rows. Which is easy enough with a Multi-Row, Crosstab, Union iterative macro. However the problem comes in the "fill down" at the bottom, which results in the rows AFTER the lowest level "child" also filling down and the concatenation reading "j" as the end of every row thereafter. 

So 1.) A solution to this particular problem would be appreciated and 2.) A way to access the back coding of the Multi-Row tool in order to access if a Multi-Row/Multi-Field tool can be created. In other words, being able to create a formula that is able to look Row-1/Row+1 AND/OR Field-1/Field+1. 

Thanks

[jarrod · Accepted solution]

See the attached example. By naming the fields in order (think x-axis) and adding a recordID (y-axis) we can transpose the data to get coordinates which allows us to reference row +-1 and field +-1. Adding a bit of logic to define which values you want to use then concatenating the results will get you what you need. 

[BPetrosilli]

This works perfectly! Now to make the suggestion to Alteryx to create a new tool that allows this in a more user friendly format so we can avoid the pain point down the road. 

[jarrod]

the great thing about macros is that it allows you to package up these sub processes and format as you desire. See my modified workflow below for the newly created Hierarchy Macro. 

Feel free to modify and use the macro as needed :)

Thanks for inspiration, this was fun to create!

[aaronlan]

How would we go about filling those values down (until a new value) with an undefined number of fields?  e.g. Field 1 would fill down "a", Field 2 would fill "b" until the "n", Field 3 "c" until the "m", and so on.

I've tried pairing it with this solution: https://community.alteryx.com/t5/tkb/articleprintpage/tkb-id/knowledgebase/article-id/326 , but ultimately is unfeasible since I'm dealing with 113k initial records (meaning the Append step exponentially turns it into 12bn records).

I couldn't get a batch macro with multi-row formula (where the control parameter updates the formula for each field), since the batch created multiple outputs instead of writing back to the same records.  A little confused about how to get an iterative macro to work here.

[jacob_kahn]

@jarrod 

AMAZING SOLUTION!

THANK YOU FOR SHARING - DEFINITELY BOOKMARKED!