Select Where a Column Contains a Value

[#1]

I have a script that is supposed to:

1) Allow the user to pick multiple files to open

2) Close any files that have no data (ie only one column with one value that says NA)

3) Close any files that do not contain 1146 in column 7 (the column is called Sealer # in some filesand is unnamed in others, further it is a character column in some files and a numeric column in others)

I have gotten the first two things working, but am struggling with the third. Here is the script that I have so far.

Files = Pick File(
	"Select JMP File",
	"$DESKTOP\Burst Data for Spencer",
	{"Excel|csv;xlsx;xls", "JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
For( i = 1, i <= N Items( Files ), i++,
	DT = Try( Open( Files[i] ) );
	DT << Set Name( Files[i] );
　
	If( N Cols( DT ) == 1,
		Close( DT, No Save ),
		Column( 7 ) << Data Type( Character );
		check = DT << Get Rows Where( Contains( Column( 7 ), "1146" ) );
		If( N Rows( check ) == 0,
			Close( DT, No Save )
		);
	);
);

To the best I can tell the problem is the the line check = DT<<Get Rows Where(Contains(column(7), "1146"));, but I can't figure out how to fix it.

Any suggestions?

[#2]

This code will work

Names Default To Here( 1 );
dt = Current Data Table();
Eval( Parse( "check = DT << Get Rows Where( Contains( " || ":" || 
	Char( Column( 3 ) << get name ) || " , \!"1146\!" ) ) );" ) );

[#3]

I tried putting the section into the script, but it doesn't seem to be wokring. It still closes thet blank files, but close the files that have data and contain 1146. I tried changing the If(NRows(check)==0 to !=0, but it didn't seem to make a difference. And this error is showing up in the log:

Unexpected ")". Perhaps there is a missing ";" or ",".

Line 1 Column 64: ...( :Sealer # , "1146" ) ) ►);...

　

The remaining text that was ignored was

Here's the updated script:

Files = Pick File(
	"Select JMP File",
	"$DESKTOP\Burst Data for Spencer",
	{"Excel|csv;xlsx;xls", "JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
　
　
For( i = 1, i <= N Items( Files ), i++,
	DT = Try( Open( Files[i] ) );
	DT << Set Name( Files[i] );
	If( N Cols( DT ) == 1,
		Close( DT, No Save ), 
 
		Column( 7 ) << Data Type( Character );
		Names Default To Here( 1 );
		Current Data Table( DT );
		Eval(
			Parse( "check = DT << Get Rows Where( Contains( " || ":" || Char( Column( 7 ) << get name ) || " , \!"1146\!" ) ) );" )
		);
		If( N Rows( check ) == 0,
			Close( DT, No Save )
		);
	);
);

[#4]

Here is a slightly modified version of your code, that lists our the values of the Get Rows Where statement and the value of check.  

Names Default To Here( 1 );
Files = Pick File(
	"Select JMP File",
	"$DESKTOP\Burst Data for Spencer",
	{"Excel|csv;xlsx;xls", "JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"",
	"multiple"
);
　
For( i = 1, i <= N Items( Files ), i++,
	DT = Try( Open( Files[i] ) );
	Wait( 0 );
	DT << Set Name( Files[i] );
	If( N Cols( DT ) == 1,
		Close( DT, No Save ), 
 
		Column( DT, 7 ) << Data Type( Character );
		Names Default To Here( 1 );
 //Current Data Table( DT ); 
		Eval(
			Parse(
				"check = DT << Get Rows Where( Contains( " || ":" || Char( Column( DT, 5 ) << get name ) || " , \!"1146\!" ) ) );"
			)
		);
		Show(
			"check = DT << Get Rows Where( Contains( " || ":" || Char( Column( DT, 7 ) << get name ) || " , \!"1146\!" ) ) );",
			check
		);
		If( N Rows( check ) == 0,
			Close( DT, No Save )
		);
	);
);

You may want to run the JMP Debugger on this.  My assumption is that there is a missmatch with the column 7 or something like that.  I know the code works, since I tested it out on a sample data table that had a column with a couple of rows that had your 1146 in the column values

[#5 · Accepted solution]

I tried debugging that script section, but couldn't ever get it to work. I ended up with this versiont that functioned with some slight modifications.

For( i = 1, i <= N Items( Files ), i++, 

	DT = Try( Open( Files[i] ) );

	Wait( 0 );

	DT << Set Name( Files[i] );

	If( N Cols( DT ) == 1, 

		Close( DT, No Save )
	, 

		Column( DT, 7 ) << Data Type( Character );

		Names Default To Here( 1 );

		name = Char( Column( DT, 7 ) << get name );

		Print( name );

		Eval( Parse( "check = DT << Select Where( Contains( " || ":" || name || " , " || "char(1146)))" ) );

		If( N Rows( DT << Get Selected Rows ) == 0, 

			Close( DT, No Save )
		, 

			DT << Invert Row Selection;

			DT << Delete Rows;

			DT << Clear Select;

//oldname = char(DT<< Get Name());

			//rename = char(Word(-1, oldname, "-"));

			//DT<<Set Name(rename);

			CD = CT << Concatenate( DT, Append to first table, Create source column );

			Close( DT, No Save );

		);
	);
);

[#6]

Hi Guys, I am trying to have script prompt the user to select 2 columns x-coordinate and another y-coordinate from a table.

Graph below is what I like the script to do. User picks the x,y coordinate then I use it to pass it on as a variable. I need it in the script later to do die mapping. any ideas? thanks graph below is photo shop to show end result.

thanks.

[#7]

I suggest that you look into the JMP Addin on "Runs Test" which contains an example of doing what you are asking to do with your example.  It provides a dialog box that allows for the selection of column and placing them into selection boxes.  It also has filtering capability and Recall capability in it.

Runs Test (Wald-Wolfowitz Test) and JSL Implementation of JMP Platform Dialog Box

[#8]

Hi Jim, can you please be more specific? is that a built in script?

Thanks

Sam

[#9]

Thx Jim, I found the addin, how do I look at the script code?

Sam

[#10]

Download the Addin to your downloads folder

Then go to JMP and

     File==>Open

Go to the download folder

Single click on the Runs Test.jmpaddin file

Then go to the down arrow for the Open button, and select

     Open using the Addin Builder

In the Addin Builder, select the Menu Items tab

Then click on the Runs Test entry

In the "Run this JSL" area, you will see the embedded script

Cut and paste it to a Script Window