Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

joshbennett
11 - Bolide
11 - Bolide

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:

 

Table with nulls.png

 

 

 

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:

 

Table with nulls filled in per action and action group.png

 

 

 

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

Nulls dups removed.png

 

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!

4 REPLIES 4
brindhan
9 - Comet

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
11 - Bolide
11 - Bolide

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
9 - Comet

@joshbennett

Haha. Keep experimenting Dexter :P 

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

 

Cheers!

pbeeram
6 - Meteoroid

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

Labels