How to extend a row selection from an If statement

[#1]

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]

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));

[#3]

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

[#4]

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?

---

[#5 · Accepted solution]

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;
		)
	)
)

[#6 · Accepted solution]

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.

[#7]

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