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

Using Offset/Replace Formula

rsmith38
5 - Atom

Hi,

 

So I'm trying to replicate a formula from excel onto Alteryx - =IF(S2="",OFFSET(A2,-1,0),S2)

 

Essentially, this formula says that if column S is blank, replace with the cell above. Is there anyway I can build this logic into Alteryx?

7 REPLIES 7
JosephSerpis
17 - Castor
17 - Castor

HI @rsmith38 you want to use a multi-row formula tool using this syntax should work IF IsEmpty([S2]) THEN [Row-1:S2] ELSE [S2] ENDIF . 

rafalolbert
ACE Emeritus
ACE Emeritus

Hi @rsmith38,

 

Another solution from me, covering when 2 or more records have empty/blank values:

 

IF IsEmpty([col1]) THEN [Row-1:col1_new] ELSE [col1] ENDIF

 

img1.JPG

 

Whereas the other suggested formula, creates this:

 

img2.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

rsmith38
5 - Atom

Thanks for your help,

when I try the formula you provided, the error message is that "Unknown Variable "Row-1:PO Number""

 

This is my formula below:

 

IF IsEmpty([PO Number]) THEN [Row-1:PO Number] ELSE [PO Number] ENDIF

 

Thanks in advance for your help

wdavis
Alteryx
Alteryx

HI @rsmith38 

 

Are you able to upload a sample or show a snippet of the data you are working with? Then I can have a look at what may be causing the error for you!

 

Thanks

Will

JosephSerpis
17 - Castor
17 - Castor

Hi @rsmith38 are you using a Multi-Row Formula tool ? The tool looks like this 

Multi_Row_Formula.PNG

rafalolbert
ACE Emeritus
ACE Emeritus

Please try this - notice the 2nd argument is actually targeting the new column name (new field being created by Multi-Row Formula):

 

IF IsEmpty([PO Number]) THEN [Row-1:PO Number_new] ELSE [PO Number] ENDIF

 

 

 

 

rsmith38
5 - Atom

Fantastic, this worked! Thanks for your help

Labels