Replacing empty value with previous value with group by criteria

Question

Hi Hello. I have this table record below.

IDDigitalIndustrialIntralogistics6129740673dBig DataMachinery 6129740673dCloud Computing  6129740673dCyber Security  63018356c37 3D ModellingLogistic IT63018356c37  Space Solution69562a28468Product Development

I would like to fill the empty rows with the value above while grouping by the ID. So for example, 'Machinery' to exist on all 3 rows of 'Industrial' where 'Digital' exists for ID 6129740673d . Similarly, for 'Industrial' column for ID 63018356c37.

Hence:

IDDigitalIndustrialIntralogistics6129740673dBig DataMachinery 6129740673dCloud ComputingMachinery 6129740673dCyber SecurityMachinery 63018356c37 3D ModellingLogistic IT63018356c37 3D ModellingSpace Solution69562a28468Product Development

I tried using Multi-Row Formula with reference to https://community.alteryx.com/t5/Alteryx-Designer-Discussions/replace-null-value-with-last-previous-value-in-the-same-column/td-p/501757 . It works but it only affects one column each time.

' if IsEmpty ([Digital]) then [Row - 1: Digital] else [Digital] endif '

Considering that I have more than 8 columns, I'm hoping there is a more feasible approach.

AkimasaKajitani · Accepted Answer

Hi @reignM ,

In this case, to proceed with some fields as the same process, you can do it to have the data on the vertical using Transpose tool.

This is the result and setting of Multi-row formula tool.

After that, you can do it to the original form using Cross Tab tool.

reignM · Accepted Answer

It works! Thank you very much!