Choose Language Hide Translation Bar
Community Trekker

## locate value and replace them

Hi everybody,

I have this type of data table (see attached file). I want to detect all values which are outside of 2 threshold values (-0.5 and 0.5) and replace them by the ceiling values. For example: -5.30, -6.00, -11.56 must be replaced by the same value of -5.00, and 5.30, 6.00, and 11.56 replaced by 5.00.

Have you any suggestion to do that in a simple way ?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: locate value and replace them

If you think about doing this with JSL there are several ways.

For example, you could simply loop over the columns and conditionally replace cells

``````dt = Data Table("test.jmp");
For(i = 8, i <= N Col(dt), i++,
ur = dt << get rows where(Column(dt, i)[] > 0.5);
lr = dt << get rows where(Column(dt, i)[] < -0.5);
Column(dt, i)[ur] = 5;
Column(dt, i)[lr] = -5;
);``````

An alternative (JMP 13) is to work with data tables as matrices using subscripts. The syntax also supports assignment, i.e. replacement of cell values. Here's an example:

``````dt = Data Table("test.jmp");
t = 0.5;                  // Treshold value
c = 5;                    // Ceiling value
nr = N Row(dt);
nc = N Col(dt);

M = dt[1 :: nr, 8 :: nc];// Matrix with all cells from col 8 to end of table
U = M <= t;              // Below upper limit?
L = M >= -t;             // Above lower limit?
//Assign treshold values to data table
dt[1 :: nr, 8 :: nc] = M :* (U & L) + c * (!U - !L);``````
2 REPLIES 2
Super User

## Re: locate value and replace them

If you think about doing this with JSL there are several ways.

For example, you could simply loop over the columns and conditionally replace cells

``````dt = Data Table("test.jmp");
For(i = 8, i <= N Col(dt), i++,
ur = dt << get rows where(Column(dt, i)[] > 0.5);
lr = dt << get rows where(Column(dt, i)[] < -0.5);
Column(dt, i)[ur] = 5;
Column(dt, i)[lr] = -5;
);``````

An alternative (JMP 13) is to work with data tables as matrices using subscripts. The syntax also supports assignment, i.e. replacement of cell values. Here's an example:

``````dt = Data Table("test.jmp");
t = 0.5;                  // Treshold value
c = 5;                    // Ceiling value
nr = N Row(dt);
nc = N Col(dt);

M = dt[1 :: nr, 8 :: nc];// Matrix with all cells from col 8 to end of table
U = M <= t;              // Below upper limit?
L = M >= -t;             // Above lower limit?
//Assign treshold values to data table
dt[1 :: nr, 8 :: nc] = M :* (U & L) + c * (!U - !L);``````
Community Trekker

## Re: locate value and replace them

Thank you very much MS,

Your first option works very well !

PA