Need help to recode a column with lab values

[#1]

Hi

In my table I have 3 columns : time, lab data and one temperature.

For the lab data I would like to keep only the value when it comes available and set all consecutive rows as null until we have a new lab result.

How should I write a script to handle this ?

Thanks

[#2 · Accepted solution]

Hi @LogitTurtle576 ,

  One possible way is to generate a new column with the following formula:

If(
	:Column 1 == Lag(:Column 1, 1), .,
	Row() == 1, :Column 1[1],
	:Column 1[Row()]
)

where :Column 1 is your data column of interest. This will set all values to null, except when there is a change from one row to the next, then it'll use that value. The middle condition sets the first row to the value of row 1 of your data column.

  I'm sure there are other ways, but I know this one can do what you're interested in doing. Plus, since you have another column, you don't lose any of your original data by recoding or overwriting the original column.

Hope this helps!,

DS

[#3]

In addition to formula solution of DS you could handle it like shown below.

First we need to sort Big Class by age (this is your lab data).

The we select the column age and let jmp find all repeating rows for age.

For these we delete the age values.

BR Georg

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA\Big Class.jmp" );
dt << sort( by( age, name ), Replace Table );

dt << select columns(age);
dt << select duplicate rows();
dt:age[dt << get selected rows()]=.;

[#4]

Couple of additional formula options (using just replace << set each value with Formula if you need one)

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(9),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 1, 2, 2, 2, 3, 3, 3])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([1.2, 1.2, 1.3, 2.3, 2.4, 2.1, 3.5, 3.6, 3.1]))
);

dt << New Column("Result", Numeric, Continuous, << Set Each Value(
	IfMZ(:Dif(:Column 1) == 0,
		.
	,
		:Column 1
	)
));

dt << New Column("Result2", Numeric, Continuous, << Set Each Value(
	If(Row() == Col Min(Row(), :Column 1),
		:Column 1
	,
		.
	);
));

[#5]

Thanks for your quick reply