Multi-row Formula not working for the last row

[donne4real]

I am trying to use the Multi-row formula to determine the value in the Determination column in the table below.

The rule is if the date is the same and Participant ID is the same but the Position is different, Determination column is Unsteady.

If the Date is the same and the Participant ID is the same and the Position is the same, then the Determination is Steady.

I have not been able to successfully get this to work. In each instance, the last value (highlighted in bold) is always showing the incorrect value.

Participant ID	Date	Position	Determination
A	3/1/2021	Gold	Unsteady
A	3/1/2021	Silver	Unsteady
A	3/1/2021	Bronze	Unsteady
B	5/2/2021	Gold	Steady
B	5/2/2021	Gold	Steady

*** I have included the workflow.

[apathetichell]

You need to add a case for what you want your if statement to do when [Row+1:your variable] doesn't exist. It's probably comparing it's entry against the void (a.k.a row+1] and that's generating a problem... Also check out your section of multi-row which defines how you want rows which don't exist to be treated. They can 0, or null() or the closest value -which could be what you are setting your multi-row for...

[DawnDuong]

Hi @donne4real 

I think you are using [Row+1] in your Multirow tool.

For this logic, I think it makes more sense to use the Row-1 instead and then set the default value to "Steady" when row doesn't exist (i.e. for the first row, there is no Row-1 of it, hence the value should be by defaut, "Steady").

This can be done by something like:

if [Row-1:Participant ID] = [Participant ID]

then if [Row-1:Date] = [Date] and [Row-1:Position] = [Position] then "Steady" else "Unsteady" endif

else "Steady"

endif

This assumes that you are setting "Values for Rows that don't exist" in the Multifield tool is NULL.

Dawn.

[DawnDuong]

Did you manage to get this worked out @donne4real ?

[donne4real]

Hello,

Thank you for your response. I am still running into the difficulty.

[apathetichell]

can you post your workflow with sample data? There's a lot of intricacy to the multi-row formula tool and without seeing your exact settings, I can't trouble shoot what's causing the error.

Here's what I have though...

[donne4real]

I have included the file. Sorry I didnt include it earlier.

[Jonathan-Sherman]

Hi @donne4real,

You're grouping on the wrong fields in your multi-row formula, you've included RecordID rather than Position. Ticking the correct three will give you the results you're looking for:

I've attached an updated workflow for you to download if needed.

Kind regards,

Jonathan

[Jonathan-Sherman]

As an additional note if you've grouped on those three fields as part of the Multi-Row formula you can remove the other two conditions within your expression:

if [Row-1:Participant ID]=[Participant ID]
then "Unsteady"
else "Steady"
endif

Which would give you the same outcome! 😊

Kind regards,

Jonathan

[donne4real]

Thank you for your timely response.

Records 1 -3 should be unsteady and #4-5 should be steady. That is part of the struggle I am having.