Fill down Latest Non-Null Value in a Column

Hi All,

I have data in below format -

I need to fill down latest non-null value. So the expected output is below

I am able to achieve this using Multi-Row tool but it works only for 1 Field at a time and I have more than 30 fields to fill. Any way to use Multi-Field tool and achieve the same.

Common Use Cases

Accepted answers

DataNath

@hemant86 most recent iteration:

Fill down non-nulls in one - v3.yxmd

All comments

DataNath

Hey @hemant86, you'll be able to handle this with a single Multi-Row Formula if you Transpose your data to start with so it all sits in a single field. You can then group by your [Name] field (originally the headers i.e. Emp Code) and fill down from there. The RecordID/sort are used to maintain the split and sort of rows when you Cross-Tab your data again. I've just used a standard Select tool to fix the name and order of fields upon output but this can be amended if it needs to be more dynamic. Hope this helps!

Quick edit: Had a bit of extra time so went ahead and built out the dynamic version. Upon first Transposing the data, this assigns an ascending integer value to each field name. When Cross-Tabbing, Alteryx sorts fields in ascending order so this is handled there. We then use a Dynamic Rename to lookup this integer value and replace it with the original field name that it corresponds to:

Fill down non-nulls in one.yxmd

Fill down non-nulls in one - Dynamic names.yxmd

hemant86

Thanks @DataNath . Will check that out in my case. Thanks again for your support.

hemant86

@DataNath The workflow you shared is working great. But when it comes to my original data set I see a issue. I guess there is something to do with the data set but not sure. I see its overwriting the Employee ID as well or filling down i guess which should not be the case since Emp ID columns are never null. Please see the sample in the screenshot below. This is happening only with my original dataset but not with the one you have shared.

Original Input (Sample) -

Output I'm getting -

If you notice, in my case the original dataset its filling down the Emp code as well. A has Emp code =2 but its overwritten as 1 similarly C has a diff Emp code but is overwritten by 2. So its messing up the whole data.

Appreciate any suggestions why this is happening.

flying008

Hi, @hemant86

There is a similar macro of filldown from gallery:

录制_2023_03_17_16_03_44_340.gif

hemant86

Hi @flying008 Appriciate if you could share the link to the macro. Not sure where to get it. I have downloaded a couple but its not working

DataNath

Hey @hemant86, any chance you can provide some more extensive sample data? I've just tried another run using the value in your screenshot above and my workflow works as expected:

hemant86

Hi @DataNath Can you please try the attached data.

DataNath

@hemant86 is this the right file? Doesn't look anything like the samples above and not all Employer IDs have names to fill down:

hemant86

Hi @DataNath this is the right file. Just that I removed other columns. Yes there will be few columns which will not have any data to fill down. So in those cases it should stay blank since we are checking only for non-null values in a particular column. If all values in a column are null then it should ignore that column and the column should stay null.

hemant86

@DataNath The workflow that you shared works for the sample data I shared earlier. Even in case of of Columns having all Null values. Problem is with this dataset that i just shared

DataNath

@hemant86 understood. Are you able to check this over? I think the groupings should work but hard to tell without a more extensive dataset.

Fill down non-nulls in one - MR.yxmd

hemant86

Hi @DataNath Can you please try the attached file. This very close to what my data set looks like. The above flow works fine for a single column but fails for multiple columns.

Book2.xlsx

DataNath

@hemant86 most recent iteration:

Fill down non-nulls in one - v3.yxmd

hemant86

Thanks a tone @DataNath This did the job for me.😊

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703