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 trouble... how to apply to multiple rows

andrewplaice
8 - Asteroid

Hi all - I think this is an easy one, but I just can't find the right syntax or way way to write this formula.  I have transaction data at the plan# level and sometimes the plan# is not included in the file, but it is always grouped by plan and if I look to the previous transaction I can find the # and I need to capture it.

 

My data occasionally will come down like this:

 

Record #

Plan #

112345 
2 
3 
412345

5

12345

6

98765

7

98765

 

I wrote the following expression, 

 

IF IsNull([Plan #])
THEN [Row-1:Plan #]
ELSE [Plan #]
ENDIF

 

But my result is only providing a value Record #2, but not repeating to number 3.  I assume it is because the calculation is being applied to 2 and 3 at the same time, and since 2 isn't solved, it can't solve 3.  I am sure there is something else I need to put in but I'm so new at this type of work I can't figure it out.

5 REPLIES 5
markcurry
12 - Quasar

Hi Andrew, your formula looks fine to me, and works for me, see attached workflow.

 

Could it be you have something ticked in the Group By options?

alts2h
7 - Meteor

Hi,

 

I cannot reproduce your issue: check the attached workflow.

Has [Plan #] a numeric type?

Otherwise, you should trim it and also check for empty string in your condition...

 

Hope this helps

mlayten
7 - Meteor

Are you sure it's a null and not an empty field?  Try isEmpty which checks either null or blank vs isNull.

 

Or are you creating a new column so that row-1 is still empty?  Make sure you are using the row-1 in the same column.

 

I've done up to 10 lines where I'm grabbing the previous value of blanks.

Bennel_Wilson
8 - Asteroid

Hi,

Your formula is fine.

 

Did alteryx explicitly show that those were Null cells. If it didn't show them as null then the cells are empty not null. These are 2 different things. Try substituting "Isnull" with "isempty".

 

Regards

Benn

 

P.S: In case this solves your query, kindly mark this as a solution.

andrewplaice
8 - Asteroid

That is so strange.  I can't find the difference between ours.  Nothing ticked there either.  I plugged your in and it works as expected... Thank you.

Labels