Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- How to extend a row selection from an If statement

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jun 23, 2019 9:56 AM
| Last Modified: Jun 23, 2019 9:57 AM
(4045 views)

How to extend a row selection from an if statement on same grouping values from an other column? In case of the attached file row 7 is selected with:

`dt << select where (:D1 > 1);`

The selection should be extend on all grouping values from column G1, which are in this case all „B“

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jun 24, 2019 7:58 AM
| Last Modified: Jun 24, 2019 7:59 AM
(3997 views)
| Posted in reply to message from Thomas1 06-24-2019

I am not sure what it is that you want to do with a formula version. That is, what values are you anticipating end up in the new column, but below is my guess on what you might want to do.

FYI, the formula version will not be as efficient as the scripting version, because it will have to execute once for every row.

```
If( Row() == 1,
dt = Current Data Table();
d1Rows = dt << get rows where( :D1 > 1 );
g1Values = {};
If( N Rows( d1Rows ) > 0,
For( i = 1, i <= N Rows( d1Rows ), i++,
Insert Into( g1Values, dt:G1[d1Rows[i]] )
)
);
);
If( Contains( g1Values, :G1 ),
x = 1;
Row State( Row() ) = Selected State( 1 );
,
x = 0
);
x;
```

Here is the data table script with the formula applied

```
New Table( "IfSelRows",
Add Rows( 15 ),
Compress File When Saved( 1 ),
New Column( "G1",
Character( 1 ),
"Nominal",
Set Values(
{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "C",
"C"}
),
Set Display Width( 76 )
),
New Column( "D1",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 0, 0, 0, 0, 0] ),
Set Display Width( 58 )
),
New Column( "Column 3",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
dt = Current Data Table();
d1Rows = dt << get rows where( :D1 > 1 );
g1Values = {};
If( N Rows( d1Rows ) > 0,
For( i = 1, i <= N Rows( d1Rows ), i++,
Insert Into( g1Values, dt:G1[d1Rows[i]] )
)
);
);
If( Contains( g1Values, :G1 ),
x = 1;
Row State( Row() ) = Selected State( 1 );
,
x = 0
);
x;
)
)
)
```

Jim

Highlighted
I'm asking for formula column because the data are updated by SQL. An existing formula column with index numbers is being updated automatically with regards to the new data.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to extend a row selection from an If statement

There are a few different interpretations of your question, but I hope the one I am solving is the correct one. Take a look at my script and tell me if it is what you were thinking of

```
Names Default To Here( 1 );
dt = New Table( "IfSelRows",
Add Rows( 15 ),
Compress File When Saved( 1 ),
New Column( "G1",
Character( 1 ),
"Nominal",
Set Values( {"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "C", "C"} ),
Set Display Width( 76 )
),
New Column( "D1",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 0, 0, 0, 0, 0] ),
Set Display Width( 58 )
)
);
// Get the rows where D1 > 1
d1Rows = dt << get rows where( :D1 > 1 );
// Create a list with the values of G1 for the rows that have been selected
g1Values = {};
If( N Rows( d1Rows ) > 0,
For( i = 1, i <= N Rows( d1Rows ), i++,
Insert Into( g1Values, dt:G1[d1Rows[i]] )
)
);
// Select the rows where any of the values in the g1List exist
dt << select where(contains(g1Values,:G1));
```

Jim

Highlighted
##

Thanks Jim for your fast reply. You got my question right. Your script delivers exactly what I was looking for.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to extend a row selection from an If statement

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to extend a row selection from an If statement

Again thanks for to code. It works as intended. What must be changed or added to the code to use it as formula column?

@txnelson wrote:There are a few different interpretations of your question, but I hope the one I am solving is the correct one. Take a look at my script and tell me if it is what you were thinking of

`Names Default To Here( 1 ); dt = New Table( "IfSelRows", Add Rows( 15 ), Compress File When Saved( 1 ), New Column( "G1", Character( 1 ), "Nominal", Set Values( {"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "C", "C"} ), Set Display Width( 76 ) ), New Column( "D1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 0, 0, 0, 0, 0] ), Set Display Width( 58 ) ) ); // Get the rows where D1 > 1 d1Rows = dt << get rows where( :D1 > 1 ); // Create a list with the values of G1 for the rows that have been selected g1Values = {}; If( N Rows( d1Rows ) > 0, For( i = 1, i <= N Rows( d1Rows ), i++, Insert Into( g1Values, dt:G1[d1Rows[i]] ) ) ); // Select the rows where any of the values in the g1List exist dt << select where(contains(g1Values,:G1));`

Again thanks for to code. It works as intended. What must be changed or added to the code to use it as formula?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jun 24, 2019 7:58 AM
| Last Modified: Jun 24, 2019 7:59 AM
(3998 views)
| Posted in reply to message from Thomas1 06-24-2019

I am not sure what it is that you want to do with a formula version. That is, what values are you anticipating end up in the new column, but below is my guess on what you might want to do.

FYI, the formula version will not be as efficient as the scripting version, because it will have to execute once for every row.

```
If( Row() == 1,
dt = Current Data Table();
d1Rows = dt << get rows where( :D1 > 1 );
g1Values = {};
If( N Rows( d1Rows ) > 0,
For( i = 1, i <= N Rows( d1Rows ), i++,
Insert Into( g1Values, dt:G1[d1Rows[i]] )
)
);
);
If( Contains( g1Values, :G1 ),
x = 1;
Row State( Row() ) = Selected State( 1 );
,
x = 0
);
x;
```

Here is the data table script with the formula applied

```
New Table( "IfSelRows",
Add Rows( 15 ),
Compress File When Saved( 1 ),
New Column( "G1",
Character( 1 ),
"Nominal",
Set Values(
{"A", "A", "A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "C",
"C"}
),
Set Display Width( 76 )
),
New Column( "D1",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 0, 0, 0, 0, 0] ),
Set Display Width( 58 )
),
New Column( "Column 3",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
dt = Current Data Table();
d1Rows = dt << get rows where( :D1 > 1 );
g1Values = {};
If( N Rows( d1Rows ) > 0,
For( i = 1, i <= N Rows( d1Rows ), i++,
Insert Into( g1Values, dt:G1[d1Rows[i]] )
)
);
);
If( Contains( g1Values, :G1 ),
x = 1;
Row State( Row() ) = Selected State( 1 );
,
x = 0
);
x;
)
)
)
```

Jim

Highlighted
I'm asking for formula column because the data are updated by SQL. An existing formula column with index numbers is being updated automatically with regards to the new data.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to extend a row selection from an If statement

I strongly suggest that if you plan on continuing your using JMP and using JSL, you need to take the time to read the JSL documentation in the Scripting Guide

Help==>Books==>Scripting Guide

Help==>Books==>Scripting Guide

Jim

Article Labels

There are no labels assigned to this post.