BookmarkSubscribeSubscribe to RSS Feed

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

cjw0

Community Trekker

Joined:

Mar 22, 2016

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

Joined:

Jul 10, 2014

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

Community Trekker

Joined:

Mar 22, 2016

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

vince_faller

Super User

Joined:

Mar 17, 2015

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

terapin

Community Trekker

Joined:

Jun 23, 2011

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.

Highlighted
markbailey

Staff

Joined:

Jun 23, 2011

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

Learn it once, use it forever!