Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
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
Highlighted
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.

 

 

 

Highlighted
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?


 

Highlighted
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

Highlighted
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

Highlighted
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
Article Labels

    There are no labels assigned to this post.