cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Thomas1
Level V

How to extend a row selection from an If statement

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
txnelson
Super User

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

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

View solution in original post

Thomas1
Level V

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

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.

View solution in original post

6 REPLIES 6
txnelson
Super User

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
Thomas1
Level V

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

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

 

 

 

Thomas1
Level V

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?


 

txnelson
Super User

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

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
Thomas1
Level V

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

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.

txnelson
Super User

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
Jim