Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula to "copy up" values?

ddiesel
13 - Pulsar
13 - Pulsar

I have a workflow that uses a multi-row formula to "copy down" values but I have a new data set that requires the values to "copy up". I've been playing around with the formula but can't quite get it to work. 

 

Formula:

IIF(isnull([Field1]),[Row-1:Field1],[Field1])

 

Sample Data:

Field1Desired
Output
[null]A
[null]A
[null]A
AA
[null]B
[null]B
[null]B
BB
5 REPLIES 5
NicoleJohnson
ACE Emeritus
ACE Emeritus

Easiest method is probably to assign a RecordID to each row, then Sort your data Descending, then use Multi-Row tool to fill down values, then re-Sort data Ascending to put it back in the right order... let us know if that does the trick!

 

Cheers,

NJ

TonyM
Alteryx Alumni (Retired)

Hi @ddiesel

 

I would use a record ID to sort so that you can fill down and then resort the data back to its original form.

 

Let me know if that helps!

jdunkerley79
ACE Emeritus
ACE Emeritus

Due to the way the Multi-Row tool works it still scans down the data but just reads one record ahead.

 

The best way I have found to do this is to use a RecordID column and to reverse the order of the dataset. Do the Multi Row imputing and then reverse.

 

Quick sample attached

 

 (same as @NicoleJohnson and @TonyM solution)

ddiesel
13 - Pulsar
13 - Pulsar

Wow!!! Many thanks to you all for the fast reply! I was able to complete the analysis while still on the phone with the requester! Needless to say, they were very impressed! Thank you!

RPS
7 - Meteor

You don't know what you don't know until you need to know 🙂
Almost 18 months of using Alteryx, I didn't need a solution to this exact problem and I reached the same place as in the original question and was stumped.  This did the trick, thank you guys.  Now I know a bit more.

Labels