Using Offset/Replace Formula

[rsmith38]

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?

[JosephSerpis]

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 · Accepted solution]

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

Whereas the other suggested formula, creates this:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[rsmith38]

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]

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]

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

[rafalolbert]

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]

Fantastic, this worked! Thanks for your help