Multi-row formula issue with numeric field

[Wynn]

Hello,

Can anyone please help me fix the issue of the attached workflow? I'm trying to have the workflow to fill in "number" column with the pre-defined numbers. For example, row 1 on number column shows P50. I want this to be filled down until P005 and the same process continues. Thanks so much for your help!

[echuong1 · Accepted solution]

You were in the right direction with the multi-row formula! 

For the configuration, you actually want to update an existing field rather than creating a new field (top section).

For the expression, essentially, we want to check if the number field is empty. If it is, take the value from the row above it. Alteryx will process records from top to bottom, so you can think of it like filling values going downward. 

Let me know if this helps! Workflow attached. You will have to update the input tool.

[LordNeilLord]

Hey @Wynn 

Try If IsEmpty([number]) Then [Row-1:number] Else [number] Endif

And set the MR Formula to Update Number column (or new column set to VWSting)

[Wynn]

Hi Neil,

Still the same issue. I also wonder why my original formula was wrong. I just wanted to understand the underlying issues as I feel like our formulas are pretty much the same

[echuong1 · Accepted solution]

Your original configuration was set to create a new field. It needs to be set to update an existing field, Number.

[Wynn]

Hi Chuong,

Thanks so much for your help! This works.I completely understand your reasoning of why you are using the empty field formula. However, if we were to use the number formula, is there a way to tweak the formulas so that they work? I just try to understand what's wrong with my formulas.

[Wynn]

@echuong1 tagging so this would send you a notification

[echuong1]

If you wanted to keep the top section of the configuration and create a new field, you can use the following:

if isempty([number])
then [Row-1:New Field]
else [number]
endif

Essentially, it's checking to see if the number field is empty. If it is not, it's using the value in the Number field in the new field created. If it is, it's using the value from the row above it in the new field. 

Also, looking at the remainder of your workflow, you're changing the datatype of the Number field to double (numeric). This field contains a string character ("P"). You'll need to remove the letter before changing the datatype to numeric. You can use a data cleansing tool set to remove letters to do this. 

[Wynn]

Thanks again for your explanation. I think my question was not clear earlier. I was wondering if we can use the following formulas 

IF IsNumber([number]) THEN [number] ELSE [Row-1:number] ENDIF

So basically if the "number" field is a number then it would keep this number currently existing otherwise it will be the number on the row above. Specifically, if row 2 on "number" column is not a number then it will read P050 on the first row and fill it in here . Then row 3 will look at row 2 and so on. Do you think this reasoning would work for this problem?

[Wynn]

@echuong1 just a follow-up. Appreciate your help!