Fill down Latest Non-Null Value in a Column

[hemant86]

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.

[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:

[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:

[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.