topic How to extend a row selection from an If statement in Discussions

How to extend a row selection from an If statement

Thomas1 — Sun, 23 Jun 2019 16:57:09 GMT

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“

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

txnelson — Sun, 23 Jun 2019 18:34:49 GMT

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

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

Thomas1 — Sun, 23 Jun 2019 21:14:53 GMT

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

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

Thomas1 — Mon, 24 Jun 2019 14:25:13 GMT

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?

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

txnelson — Mon, 24 Jun 2019 14:59:36 GMT

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

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

Thomas1 — Mon, 24 Jun 2019 18:28:16 GMT

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.

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

txnelson — Mon, 24 Jun 2019 19:01:41 GMT

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