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
Samuel11
Level I

Get rid of null columns

Hi All,
I need you help with this problem,
Attached is a picture of the table I am working on.
I want to get rid of any empty column. Note: whenever row 2 =0 the column is empty.
which in this case is starting at column 12 to the end of the table.
I would really appreciate all you  help,
Thank you,jmp-task.png

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Get rid of null columns

Here's a way to do it in code.

dt = New Table( "Untitled 3", Add Rows( 10 ), 
	New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ) ),
	New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 7", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 8", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 9", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 10", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 11", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 12", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 13", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 14", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 15", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 16", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) )
);

nc = ncols(dt);
for (i = nc, i >= 12, i--,
	if (column(dt, i)[2] == 0,
		show(i);
		dt << delete columns(i);
	);
);

View solution in original post

2 REPLIES 2

Re: Get rid of null columns

Is this table typical? That is, you expect the same layout each time in import such a worksheet? If so, you can use the Excel Import Wizard to define such things as the row(s) with the column names (e.g., row 12), the row where the data starts (e.g., row 13), the first data column (e.g., column 1), and the last data column (e.g., column 11).

 

Another way is to use the JMP add-in for Excel. Click the Preferences button on the JMP ribbon and define the table. Select the sub-table in the Excel worksheet (including, for example, rows 12 to the end of the data and columns 1 to 11) and then click the Export button on the JMP ribbon.

 

In this case, you could also just select the data you want in the Excel worksheet, copy it, go to JMP and make a new empty data table, and select Edit > Paste With Column Names (Control-Shift-V on Windows, Command-Shift-V on Macintosh).

 

You won't have to clean up the JMP data table because the parts that you do not want won't be imported in the first place.

pmroz
Super User

Re: Get rid of null columns

Here's a way to do it in code.

dt = New Table( "Untitled 3", Add Rows( 10 ), 
	New Column( "Column 1", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ) ),
	New Column( "Column 2", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 3", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 4", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 5", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 6", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 7", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 8", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 9", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 10", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 11", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 12", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 13", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 14", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 15", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., ., ., ., ., ., ., ., ., .] ) ),
	New Column( "Column 16", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [., 0, ., ., ., ., ., ., ., .] ) )
);

nc = ncols(dt);
for (i = nc, i >= 12, i--,
	if (column(dt, i)[2] == 0,
		show(i);
		dt << delete columns(i);
	);
);