Fill Down Over Null if Defined Value Exists Within Group / Unique ID

[joshbennett]

In the dataset resulting from my current workflow, I have a table with null values for many fields. I created the following simplified representation of this table in Excel for illustrative purposes:

For each unique group, which should be considered the unique combination of each "Action Group" and "Actions" fields (e.g., Table Actions - TableAction1, Table Actions - TableAction_2, Data Actions - DataAction_1, Data Actions - DataAction_2), I want to replace null values with non-null values for the same field, but only if that non-null value exists for the same unique ID (i.e., Action Group + Action) combination. If no non-null value exists for the field within the same Action Group + Action combination, I want it to stay as null.

I can safely assume (based on how the data gets to this point in my workflow) that only one non-null record per unique ID (i.e., Action Group + Action) will exist for each field. For example, I know there will only ever be 1 record for Table Actions - TableAction_1 where TableAttribute1_1 has a non-null value (which is "02" in the example table above), so I do not have to worry about any instances where I would need Alteryx to choose between 2+ non-null values to replace the null value within the field.

If I were to manually replace each field's null values with the non-null values that exist for the same unique ID (i.e., Action Group + Action) combination, the result would look like this:

After this, my final desired output would be achieved by simply using the Unique tool to remove duplicates, which would result in this:

I cannot figure out how to do this in Alteryx. I have found multiple helpful threads that talk about filling down over null values with the value of a record before / after using the Multi-Row Formula tool, but I cannot figure out how to conditionally fill down over null based on the criteria I explained above.

Also, if at all possible, I would like the solution to work on a variable number of columns with potential null values (that could be more or less than my example above based on what is contained in the source input files earlier in my workflow, which may change between uses of the workflow) without having to create separate solutions for each column / field with potential null values each time I use the workflow, if that makes sense. I found this thread which addresses how to fill down over null for a variable number of columns / fields, but it only allows for replacing null values based on the row immediately above versus conditionally replacing the null based on the criteria I explained above.

Any help would be much appreciated - been stumped by this one so far.

Thanks in advance!

[brindhan · Accepted solution]

Hi @joshbennett

I've replicated your requirement and built a quick workflow.

In case of issues, do send me a sample of your data.

Hope this helps.

Cheers,

Brindha

[joshbennett]

Wow, don't I feel like a dunce - such an easy and elegant solution.

You, sir/madam, are a gentleman/woman and a scholar. I tip my hat to you.

Many thanks!

[brindhan]

@joshbennett

Haha. Keep experimenting Dexter :P 

And for everything else, the Alteryx community here is truly awesome.

Cheers!

[pbeeram]

@brindhan I had similar issue and your solution worked for me. Thank you!