Creating a Multi-Field "Fill Down" Macro

[danielkresina]

Anyone working with Excel data inevitable encounters the situation where data needs to be "filled down" to blank rows below values.

Suppose, for example, that you wanted to convert this data:

Into this:

The typical approach would be to use the Multi-Row tool with a basic formula such as IIF(ISNULL([Field1]),[Row-1:Field1],[Field1]) which essentially returns the value of the row above whenever the current row is null.  This works great, however if I wanted to do this for Fields 1 through 4, I'd have to repeat the multi-row tool four times, rebuilding the formula each time.  You can imagine how combersome this would get with 20 columns of data.

My goal, then, is to build a macro that would allow me to select which fields I'd like to "fill down", and it would take care of the rest.

My plan was to use an outer macro which would get a list of the incoming fields and provide the user with a list box to check off which fields they wanted to fill down.  It would then call a batch macro, passing in each field name, and the batch macro would update the expression in a multi-row tool and process the data for each field the user selected.  The final output of the whole macro would look identical to the incoming stream of data, except that all the null values in the selected columsn would be filled in.

I'm running into odd errors that are dificult to debug since it's hard to "step through" things in Alteryx.

Has anyone done something like this?  The basic concept could be useful for many things, because it's essentially enabling a specific tool to be run against several fields that a user selects.

[chris_love]

This thread is a useful starting point to finding a solution:

http://community.alteryx.com/t5/Data-Preparation-Blending/Multi-field-multi-Row-formula/td-p/4938

[RodL]

Also as far as debugging macros, you might want to look at this post (which directs you to another one with the details)...

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Macro-Debugging-Use-Another-Macro/ta-p/1149

[danielkresina · Accepted solution]

Thanks All.  Per the thread Chris referred me to, I gathered that a batch macro approach wouldn't work right and it was better to handle via a transpose operation.  Attached is the final macro.  Any feedback welcome.

I posted a more in-depth description on my blog here:  http://sculptingdata.com/alteryx/building-a-fill-down-macro-in-alteryx/

The result is really handy.  All you have to do is drop the macro into your stream, check off the fields you want to "Fill down", and you're done!

[alicezyk]

This is amazing! Super helpful! I encountered one problem using this as I am trying to group by 2 other columns.Not sure how I should proceed in this case. It will probably over complicate the process. Your advice will be highly appreciated!

---- update:

figured out myself!

---

@danielkresina wrote:

Thanks All.  Per the thread Chris referred me to, I gathered that a batch macro approach wouldn't work right and it was better to handle via a transpose operation.  Attached is the final macro.  Any feedback welcome.

I posted a more in-depth description on my blog here:  http://sculptingdata.com/alteryx/building-a-fill-down-macro-in-alteryx/

The result is really handy.  All you have to do is drop the macro into your stream, check off the fields you want to "Fill down", and you're done!

---

[MaverickVZ]

Really helpful...Thanks!!

[Cal_A]

Found this approach useful - thank you.

I do think that it is a feature that should be added by Alteryx, especially given your notes about performance with large data-sets.  If we compare to standard Pandas tools I always feel it should be easier in Atleryx, but this is an example where Pandas is simpler for once.

[janety0127]

Hi,

How can I accomplish the same outcome when my Field 1 column is less structured like the below?

[nathanf4]

I would create a record ID of the original file maybe "original order". Then sort it by week ascending.

Then create another record ID "Section ID" create a formula changing section ID: if week = 1 then Section ID else "" endif.

then sort by "Original order" ascending. Then use a multirow formula on your "section id" and fill it down if current row = "" then row-1 else current row endif. 

Now you have sections for each week. Summarize tool: group by section id and Max "Field 1" 

Then add a join tool: left side comes from the summarize tool and the right side comes from the tool right before summarize. Join on section ID. You should just need to use an inner join. Set that max field 1 value to "Field 1".

That should accomplish what you are trying to do. This won't work if you have two different values for a given grouping of weeks.

Let me know if you that doesn't make sense.