Hi All, I have a data set that I need to clean up before I can use it in a flow. The data is a cut and paste of a pivot table display of data where the field values are not repeated. For example (Note that my actual data has about 50 fields): RecordNameContractStatusStart DateEnd dateValue1BobX123Draft Amendment1/1/202331/01/20231 2[Null][Null][Null]1/1/202131/01/20211003[Null][Null]Published1/2/202331/12/202314[Null][Null][Null]1/2/202131/12/20221005[Null]X456Draft1/1/20221/2/2023106[Null] [Null]Published 2/2/202331/12/2025 [Null]7FredX234Published1/1/20221/1/2026500 The top row are the Field names, except for "Record" I need to update each field in a row, so that the field value, if null, is equal to the previous Row value. For example: * Record 2, the Field [Name] value is [Null], so Record 2 [Name] should update to "Bob" from Row 1, field [Name]. * Record 2, the Field [Contract] value is [Null], so Record 2 [Contract] should update to "X123" from Row 1, field [Contract]. * This should continue across all the fields in Row 2. * Record 3, the Field [Name] value is [Null], so Record 3 [Name] should update to "Bob" from Row 2, field [Name]. - assuming it was updated in the previous step. * and repeat for all fields and all rows. So the resulting table would look like this: RecordNameContractStatusStart DateEnd dateValue1BobX123Draft Amendment1/1/202331/01/20231 2BobX123Draft Amendment1/1/202131/01/20211003BobX123Published1/2/202331/12/202314BobX123Published1/2/202131/12/20221005BobX456Draft1/1/20221/2/2023106BobX456Published 2/2/202331/12/2025 107FredX234Published1/1/20221/1/2026500 I know I can do this using the the Multi-Row Formula tool, but I only know how to do this on an individual field basis, so I would have to replicate the tool 50 times or for as many Fields as I have. I assume that a Batch Macro might work, but I'm stuck on how to start that. Cheers Craig

Multi-Row Formula to duplicate row data across all Fields in a data set

Hi All,

I have a data set that I need to clean up before I can use it in a flow. The data is a cut and paste of a pivot table display of data where the field values are not repeated.

For example (Note that my actual data has about 50 fields):

Record	Name	Contract	Status	Start Date	End date	Value
1	Bob	X123	Draft Amendment	1/1/2023	31/01/2023	1
2	[Null]	[Null]	[Null]	1/1/2021	31/01/2021	100
3	[Null]	[Null]	Published	1/2/2023	31/12/2023	1
4	[Null]	[Null]	[Null]	1/2/2021	31/12/2022	100
5	[Null]	X456	Draft	1/1/2022	1/2/2023	10
6	[Null]	[Null]	Published	2/2/2023	31/12/2025	[Null]
7	Fred	X234	Published	1/1/2022	1/1/2026	500

The top row are the Field names, except for "Record"

I need to update each field in a row, so that the field value, if null, is equal to the previous Row value. For example:

Record 2, the Field [Name] value is [Null], so Record 2 [Name] should update to "Bob" from Row 1, field [Name].
Record 2, the Field [Contract] value is [Null], so Record 2 [Contract] should update to "X123" from Row 1, field [Contract].
This should continue across all the fields in Row 2.
Record 3, the Field [Name] value is [Null], so Record 3 [Name] should update to "Bob" from Row 2, field [Name]. - assuming it was updated in the previous step.
and repeat for all fields and all rows.

So the resulting table would look like this:

Record	Name	Contract	Status	Start Date	End date	Value
1	Bob	X123	Draft Amendment	1/1/2023	31/01/2023	1
2	Bob	X123	Draft Amendment	1/1/2021	31/01/2021	100
3	Bob	X123	Published	1/2/2023	31/12/2023	1
4	Bob	X123	Published	1/2/2021	31/12/2022	100
5	Bob	X456	Draft	1/1/2022	1/2/2023	10
6	Bob	X456	Published	2/2/2023	31/12/2025	10
7	Fred	X234	Published	1/1/2022	1/1/2026	500

I know I can do this using the the Multi-Row Formula tool, but I only know how to do this on an individual field basis, so I would have to replicate the tool 50 times or for as many Fields as I have.

I assume that a Batch Macro might work, but I'm stuck on how to start that.

Cheers

Craig

Datasets

Developer

Macros

Accepted answers

philipmannering

The usual solution to this kind of problem is pivoting your data,

Multi-Row Formula for all rows.yxmd

All comments

philipmannering

The usual solution to this kind of problem is pivoting your data,

Multi-Row Formula for all rows.yxmd

philipmannering

You can also do this in the Python Tool with the following code,

from ayx import Alteryx
df = Alteryx.read('#1')
df = df.ffill()
Alteryx.write(df, 1)

SharkeyNZ

Thanks, that works perfectly and I see the logic I was missing in other examples.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703