Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

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

Data cleansing - multi-row formula help

dataviolet
7 - Meteor

Hi all! I have some messy data below that I am trying to tag with an account code. Is anyone able to help me with how I should update a multi-row formula in order to get the column in red below ("What I want")? I tried doing this: iif([Value]!=null(),[Row+1:Code],null()), but instead of replicating the Code everywhere there is a value (value not null), it is shifting the Code 1 row up.

 

 

DeptDescriptionValueCodeWhat I want
     
 Balance Forward   
     
     
     
 *** FISCAL YEAR END *** Opening Balance for Year0 110105-1201
  0 110105-1201
** Account Totals 0110105-1201110105-1201
 110105-1201   
 Balance Forward   
     
     
 Opening Balance for Year0 110105-1501
  0 110105-1501
** Account Totals*** FISCAL YEAR END ***0110105-1501110105-1501
 110105-1501   
     
 Balance Forward   
     
     
 Sample Data0 110105-1506
  0 110105-1506
 Sample Data0 110105-1506
  0 110105-1506
 Sample Data0 110105-1506
 Sample Data0 110105-1506
 Sample Data0 110105-1506
  0 110105-1506
 Sample Data0 110105-1506
  0 110105-1506
** Account Totals 0110105-1506110105-1506
 110105-1506   
4 REPLIES 4
fmvizcaino
17 - Castor
17 - Castor

Hi @dataviolet ,

 

Here is a suggestion for you.

fmvizcaino_0-1609880175091.png

 

 

Best,

Fernando Vizcaino

echuong1
Alteryx Alumni (Retired)

The multi-row formula will process rows from top to bottom, so it makes it difficult to reference values with a varying number of rows that you're trying to grab values from. An easier way to get around this is to reverse the order of your records (since the value you're trying to grab will now be at the top). You can add a record ID and sort by this to reverse the data back and forth.

 

For the multi-row formula, you can use a conditional statement that says if the value is filled in and the code is missing, take the code from the row above.

 

echuong1_0-1609880286040.png

 

pedrodrfaria
13 - Pulsar

Hi @dataviolet 

 

See below for my attempt. We invert the order to be able to better utilize the multirow. It becomes much easier.

 

pedrodrfaria_0-1609881042587.png

Pedro.

dataviolet
7 - Meteor

thank you all for the help! looks like the trick is to reverse the order. I really appreciate the explanations and solutions from everyone!

Labels
Top Solution Authors