How to fill null values with data in a later row

Hi all, fairly new to Alteryx.

I have a set of data with thousands of Company names, each company name having multiple rows of data.

I currently have done a summarize and union function to create new columns that would basically give me a sub-total for each company.

Example:

Company 1 Data

Company 1 Sum Data

Company 2 Data

Company 2 Sum Data

I am using the multi-row formula function to fill in the 3rd column of data with the "Sum Data" value.

alteryx help.PNG

My Problem is the new data looks like this....

Example:

Company 1 Data

Company 1 Data Sum Data

Company 1 Sum Data

Company 2 Data

Company 2 Data Sum Data

Company 2 Sum Data

As you can see, it is only working for the row RIGHT ABOVE the summarize column value.

Any help is welcome!

Common Use Cases

Accepted answers

Philip

The only way I know how to do this is to add a RecordID field, reverse sort using RecordID Descending, perform the forward fill, then resort using RecordID Ascending.

Backwards Fill.png

Backwards Fill.yxmd

All comments

Philip

The only way I know how to do this is to add a RecordID field, reverse sort using RecordID Descending, perform the forward fill, then resort using RecordID Ascending.

Backwards Fill.png

Backwards Fill.yxmd

NicoleJohnson

Reading through your scenario, I think you have two options here to achieve the visual subtotals you're looking for:

1. If formatting isn't as imperative, try some of the built-in tools that Alteryx uses for summarizing: the Summarize tool would allow you to group by company and then sum up the data (but you'd lose your detail, since it would just show one line with a total per company)... or the Running Total tool, where you can group by company and then show a running total per line. But keep in mind this would put the "subtotals" in the column next to the data, rather than in a new line at the bottom of each company...

2. If you need to force your data to appear in more or less the same format we're used to seeing when using programs like Excel (subtotals, pivot tables, etc.), then you have to get a bit more creative - Alteryx likes to put everything in very basic tables where each line is a record (or a subtotal, etc. depending on your workflow). So this makes the more "visually appealing" subtotals that we're all used to seeing in Excel/etc. a little trickier to display... Not impossible, however (few things are with this program, as I'm sure you will discover for yourself as you continue to explore!) So if you need it formatted this way, try something like the following to get your "subtotals"...

1. Add a new line to separate your companies using the Multi-Row tool + Generate Rows (this will be your future Subtotal line)

2. Remove unnecessary info & put NULL values for duplicate Data from the newly added subtotal row

3. Calculate Running Total, grouping by Company

4. Remove unnecessary info & put NULL values for every row except the last row per company (i.e. the new "subtotal" line).

There are a few different ways you could reach the same result for option #2 (I can think of several methods using tools like unions & record ID's), but the method above/attached might work for you. If not, let us know where we can tweak it!

Cheers,

David8.yxmd

patrick_digan

@David8

I currently have done a summarize and union function to create new columns that would basically give me a sub-total for each company.

Perhaps I'm not reading/interpreting your post correctly, but couldn't you use a join before the union? I've attached a quick sample where if you summarize your data grouped by company, you can join it back on company to get the subtotal for each company on each line. Then I did a union to get a subtotal line. I added a recordID to help with ordering things properly. Hope that helps!

Summarize Join.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703