Allow using limits in Missing Value Codes or provide new column property with same functionalityI would like to have new column property (or modification to existing one) which would allow me setting upper/lower limits and then all the values outside those limits would be treated as missing values.
I have situations where I have hundreds of measurement columns. In some rows single value might be a failed measurement and to easily exclude it from analysis I have two options:
- Exclude whole row, but this will make me loose measurement data from all the good measurements also
- Stack the data and handle those situations row by row exclusion. This will make it much more difficult to use some of the JMP platforms, column switcher, data filters and so on.
My suggestion is to add column property which I could use to "exclude" (treat as missing) those values from single column. This way I could fairly easily loop over the data and set my limits. Maybe setting this column property could also be added to Explore Outliers platform.
Edit:
So something like this but without formulas:
View more...
Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
Column(dt, "height") << Set Property(
"Missing Limits",
{50, 65}
);
key = "height";
m_temp = Column(dt, key) << get values;
Eval(EvalExpr(Column(dt, key) << Formula(
m = Expr(m_temp);
If((Expr(Name Expr(As Column(key))) << Get Property("Missing Limits"))[1] < m[Row()] < (Expr(Name Expr(As Column(key))) << Get Property("Missing Limits"))[2],
m[Row()]
,
.
);
)));
wait(1);
Column(dt, "height") << Set Property(
"Missing Limits",
{59, 61}
);
dt << Rerun Formulas;
wait(1);
Column(dt, "height") << Set Property(
"Missing Limits",
{50, 65}
);
dt << Rerun Formulas;
Data AccessData Blending and CleanupQuality and Process EngineeringRe: Allow using limits in Missing Value Codes or provide new column property with same functionality - Status changed to: Yes, Stay Tuned!Re: Allow using limits in Missing Value Codes or provide new column property with same functionalityGreat, thanks @jthi!
Re: Allow using limits in Missing Value Codes or provide new column property with same functionalityUsing the existing Missing Value Codes column property should work great.
Re: Allow using limits in Missing Value Codes or provide new column property with same functionalityHi @jthi , would this solution, suggested by the developer, suffice?
"We could augment the existing “Missing Value Codes” column property to allow a range of values as missing (for numeric columns only)"
Re: Allow using limits in Missing Value Codes or provide new column property with same functionalityFailed measurements was just an example. More realistic example (for my purposes) would be outliers which I would like to temporarily exclude from wide formatted data. Range Check would work fine if it didn't modify the values in the table, that is a feature I do not want (not a problem with failed measurements, but otherwise it would be).
Re: Allow using limits in Missing Value Codes or provide new column property with same functionalityHi @jthi, the developer reminded me that you can use the Range Check column property for this. "If you set Range Check on a column, it will turn values outside the range into missing. Unlike the proposed property, this will actually change the values. But since they're failed measurements anyway, would it be sufficient?"
Re: Allow using limits in Missing Value Codes or provide new column property with same functionality - Status changed to: InvestigatingRe: Allow using limits in Missing Value Codes or provide new column property with same functionality - Status changed to: Acknowledged@jthi - Thank you for your suggestion! We have captured it and will take it under consideration.
