Discussions
Solve problems, and share tips and tricks with other JMP users.
Hi all,
I'm trying to compare across rows in my data table in a generalizable manner. To do this, I'd like to create a formula column that finds and returns a value from a specific row with certain traits.
In this example screenshot, I would like the "Control Result" to be a formula column that returns the "Control" row with a matching "Donor" field, and return the corresponding "Result" score. I would like to use Formula Editor for this, and the most intuitive way I can think to return data from another row is to use Subscript.
However, I am not sure how to properly create the logic for "i = (lowest) row with matching Group and Condition='Control'". What formula block (or combination thereof) can be used to return "i = the row number that matches X conditions"? Might there also be some byVar trick I should use here?
As an aside: I know Match is another option, however it seems to require writing out the "Control" values in the formula block itself (or bespoke Subscript calls) for each Group. I am trying to replicate this workflow across a much larger dataset and would prefer to minimize hardcoded values. I also work with a lot of non-coder collaborators and would like to avoid JSL; and only need to swap out the Condition and Result fields as necessary using the Formula Editor.
Thank you!
Here is the formula I came up with:
Col Cumulative Sum( If( :Condition == "Control", :Result ), :Group )