Multi Row add 1 to current value dependent on other column value

[HenpetsGordres1]

Hello,

I need to create the 'Defect #' column as shown below, I assume this is relatively easy with the Multi Row Formula tool, but can't write the correct syntax

I tried the following:

IF [Result] = "Fail" THEN 1 ELSE Null()
ELSEIF [Row-1:Defect #] != Null() THEN [Row-1:Defect #]+1
ELSE Null()
ENDIF

Many thanks

[BenMoss · Accepted solution]

Hi @HenpetsGordres1 , I think the best thing to do here is a 3 step process.

1) isolate the 'Fail' results using a filter tool.

2) On your fail stream, use a record id tool to create an incremental ID for each of the fails.

3) union part 2 with the 'Pass' isolated records from step 1.

You could use the multi-row formula tool to do part 2, but in your case that doesn't seem necessary.

Ben

[danilang]

Hi @HenpetsGordres1 

As an alternative to @BenMoss's solution, you can use the following

 

The multi row formula is this

if [Result] = "Pass" then
	[Row-1:Defect #]
Else
	[Row-1:Defect #]+1
Endif

If "Pass" copy the previous value(starting at 0).  If not, add one to it. 

The formula tool sets the Defect# to Null() if its a pass, giving you

 

Dan