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

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

terapin — Tue, 13 Aug 2019 17:32:19 GMT

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

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

txnelson — Tue, 13 Aug 2019 17:34:25 GMT

Here is one version of the formula that works:

If( Row() == 1, lagval = . ); 
x = Sum( :col 1, lagval ); 
If( Is Missing( col 2 ) == 0, 
       lagval = :col 2 
);
x;

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

Jeff_Perkinson — Wed, 19 Oct 2016 02:27:04 GMT

I think there's an error in your description that we'll have to clear up before we can give you a precise solution. Your conditional says if that if Col1 is NOT MISSING then add it to the lagged value of Col2, and if Col1 IS MISSING then add that to a previously stored value of Col2.

You can't add a missing Col1 to anything and get anything other than missing.

Having said that, I would build this table out one column at a time. Specifically, I would create a "non-missing Col2" column which had no missing values using the formula:

This formula sets "non-missing Col2" to Col2 when Col2 isn't missing and sets it to the Lag of "non-missing Col2" when Col2 is missing.

You can then use a Lag(non-missing Col2, 1) in your final formula.

I hope that helps.

-Jeff

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

terapin — Wed, 19 Oct 2016 02:27:13 GMT

Thanks Jim and Jeff,

I think I made the issue more complicated than it needs to be in my description and table layout and can see how that may have led you to your suggestions.

While I understand your suggestion Jeff about creating a new column that holds the non-missing data I'm hoping to accomplish my work without creating new columns since I'll be using this formula for many different columns. Let me try to be more clear about what I'm trying to accomplish by way of the table below.

I'm trying to create a running sum of Column 1 in Column 2. Without creating any additional columns, I was hoping to create a variable that contains the current running sum value in Column 2 and adds that to the value of Column 1.

My issue is not knowing the best way to create and use a variable that contains the present value for Column 2 so that this value can be "carried" across rows of missing Column 1 data. Column "Result I'm Looking For" shows what I would like the formula in Column 2 to produce. Hopefully this description is a bit more clear about what I'm looking to accomplish.

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

vince_faller — Wed, 20 Apr 2016 21:03:32 GMT

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

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

jerry_cooper — Wed, 02 May 2018 14:49:26 GMT

I think this formula will give you what you're after:

If( Is Missing( :Column 1 ),
	:Column 1,
	Summation( i = 1, Row(), :Column 1 )
)

Hope this helps.

-Jerry

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

terapin — Mon, 25 Apr 2016 18:00:28 GMT

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

markschahl — Wed, 02 May 2018 14:24:36 GMT

I run into this problem all of the time and use the solution give by Jerry. I analyze process measurements (temperature, pressure, flow, etc.) that are retrieved from a compressed-data-historian. Every few thousand points, there can be a missing value.

Example below calculates if reactor is running or not based on 3 point moving average of catalyst flow, F.Cat >= 11 (anonymized yes, you get the reference...). If F.Cat is missing, calc uses previous row's value of "Running?".

If(
	Row() < 3, 0,
	Is Missing( :F.Cat ), Lag( :Name( "Running?" ), 1 ),
	(Lag( :F.Cat, 2 ) + Lag( :F.Cat, 1 ) + :F.Cat) / 3 >= 11, 1,
	0
)

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

cjw0 — Fri, 27 Apr 2018 14:27:38 GMT

I think this is exactly what I need as well, but I don't fully understand the script....is it possible to show what this would look like in the formula editor...

thanks so much

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

jerry_cooper — Wed, 02 May 2018 14:00:14 GMT

I believe this response was written prior to the release of JMP 13 which introduced the Col Cumulative Sum() function. If you are using v13 or higher, all you need do to create a cumulative sum column is right-click the column header for the column you wish to sum and select New Formula Column=>Row=>Cumulative Sum. This will create a new column with the appropriate formula. To see the formula in the editor, right-click the new column and select Formula.

If you are using a prior version, copy and paste the following statement into the formula editor to see what it looks like:

Summation(i=1, Row(), Your Column[i])

Hope this helps.

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

cjw0 — Tue, 13 Aug 2019 17:36:31 GMT

What I am tyring to do is ask the question....what is the date difference between two different biopsies (column labelled TCMR) for a given patient (pt number in first column) in my database. The biopsies occur at irregular intervales and the outcome of the biopsy is shown in the third column below.

Although I know how to retrieve this information if I simply create a subset of only the rows which contain a biopsy result and then ask for the date difference....what I would likel to do is figure out a way to determine the date difference between any to rows that have a biopsy result while ignoring the empty rows.

@jerry_cooperwrote:

I think this formula will give you what you're after:
If(Is Missing(:Column 1), :Column 1, Summation(i = 1, Row(), :Column 1))
Hope this helps.

-Jerry

Can you help with this. Ideally with a solution that I can see in the formula editor or can paste into it.

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

jerry_cooper — Sat, 28 Apr 2018 16:08:05 GMT

Unfortunately, I can't see the example table you provided, however, the enclosed file might give you an idea for an approach to take the difference between the current and previous non-missing dates.

The formula in the second column first establishes a matrix of rows for the non-missing cells in the Date column. This provides a way to reference the previous, non-missing row which is used as an index in the Date Difference function.

I'm sure there are other ideas for doing this...

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

cjw0 — Tue, 01 May 2018 21:07:40 GMT

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,

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

Mark_Bailey — Wed, 02 May 2018 13:27:38 GMT

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

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

jerry_cooper — Wed, 02 May 2018 16:00:39 GMT

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.

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

cjw0 — Mon, 07 May 2018 18:20:25 GMT

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

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

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

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