cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
terapin
Level VI

Formula to use previous value if not missing, otherwise use value from last occupied cell

I'm trying to write a formula in Col3 that adds the current value of Col1 with the lagged value of Col2 but I'm running into a pickle trying to figure out how to carry forward the last observation from Col2 when missing data is encountered and to use this value at the first instance of non-missing data.  That is,

 

 

If( !IsMissing( Col1)
     Col1 + Lag(Col2,1);
     //create variable that contains current Col2 value;

//Else
     Col1 + previously stored value from Col2;

);

 

I would appreciate any suggestions on how best to accomplish this.  Thanks.

 

Row# Col1 Col2 Col3
1 2 1  
2 1 3 2
3 1 5 4
4 2 7 7
5 . .  
6 . .  
7 . .  
8 2 8 9
9 2 9 10
10 1 11 10
11 . .  
13 . .  
14 2 12 13
15 2 13 14
16 1 14 14
17 2 15 16
15 REPLIES 15
cjw0
Level II

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

Thanks Jerry.  This does look like exactly what I need.

Unfortunately I am a novice at this and I still need help figuring out how I can re-create this myself in my own database and the formula editor. I have attached a very simplfied version of the column's of interest for a single patient in my data.

 

In my database there are many rows of data with lab values that we are following, however, only some of the rows have a biopsy result. I want to create a formula that will use the dates in the "Dates" column to calculate the date difference between the biopsies whenever they occur.

 

Unfortunately I don't understand which functions you have used to create your formula.... for example... although if have used the "Is missing" function many times I don't even see the option of "Loc Nonmissing" in the formula editor.

Also, what does "nm" mean.... I assume "something matrix" but where do I find that in the formula editor?

 

Sorry for the hassel but I am axious to learn this as it looks like exactly what I need.

 

thanks again,

 

CW 

jerry_cooper
Staff (Retired)

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

Sorry I didn't provide more of an explanation in my previous response. First, to make the formula work for your data table, change the "Date" column in the "If" statements to "Biopsy Result" and the "Hour" argument in the Date Difference function to whatever interval you need (i.e. "Day"):

If( Row() == 1,
	nm = Loc Nonmissing( :Biopsy Result << get values );
	.;
,
	If( Is Missing( :Biopsy Result ),
		.,
		Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) )
	)
)

Now for the explanation. The existing formulas don't automatically index backward or forward until they find non-missing data, so I resorted to scripting to create variables that contain the information needed. If you would like to learn more about scripting, the Help->Books->Scripting Guide and Help->Scripting Index are great resources. 

 

The ":Biopsy Result<<Get Values" statement generates a list with all the values for Biopsy Result. Loc Nonmissing is a matrix function that finds all the positions in the list that are non-missing. The variable, "nm" stores this result, which now contains the row numbers for the non-missing Biopsy Result entries. Since this only needs to be created once, it is done for the first row only. Also, the result is set to missing for the first row.

Next, if Biopsy Result is missing, the result is set to missing, otherwise, we need to reference the previous, non-missing Date for the Date Difference calculation. Contains(nm, Row()) finds the position of the current row in the "nm" matrix. Subtract 1 from this to find the position of the previous row in the "nm" matrix. This now is the index, i.e. row number, for the Date value in the row with the previous, non-missing Biopsy Result. The "Try" function ignores the error generated by the Date Difference function when there is no previous, non-missing row (i.e. the first instance of a Biopsy Result). 

 

A couple of things to keep in mind, this assumes your data are sorted by date within ID # (as in your example). Also, if you have multiple ID #'s in your data table, you may want to add another condition so that you're not comparing two different ID's. In this case, your formula/script might look something like this:

If( Row() == 1,
	nm = Loc Nonmissing( :Biopsy Result << get values );
	.;
,
	If( Is Missing( :Biopsy Result ),
		.,
		If( :ID # == :ID #[nm[Contains( nm, Row() ) - 1]],
			Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) ),
			.
		)
	)
)

I know this is a lot of info, but you did say you were "anxious to learn this"... hope this helps.

cjw0
Level II

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

Thank you Jerry for your great explaination and for anticipating my next question of incorporating the ID's!

vince_faller
Super User (Alumni)

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

Have we always been able to have a column Formula be self-referential?  I never realized I could do this. 

Vince Faller - Predictum
terapin
Level VI

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

Thanks everyone for your comments and help. 

I think Jerry's suggestion works the best for me and didn't require the use of a local variable which is what I was wanting to use.

Re: Formula to use previous value if not missing, otherwise use value from last occupied cell

You might find the Data Table Tools add-in helpful for such cases.