BookmarkSubscribe
Choose Language Hide Translation Bar
MuttonChops
Community Trekker

How do I delete rows of variable column numbers when all are missing data

I have an auto-generated dataset and some of the columns, those that have a substring "X_Clean" in it, will have some null values.  Sometimes there are 2, 3, 4 or even 5 different columns, depends on the dataset.  I want to delete the rows only when ALL "n" of these "X_Clean" columns have null values but never if at least one of them is populated with a value.

 

I was thinking of doing a FOR loop to generate the column list of interest, and this works.

 

col_list = dt <<get column names(string);
	for(i=nitems(col_list), i>0, i--,
		if(!contains(col_list[i], "X_Clean"),
				remove from(col_list, i);
		));

But then I'm not sure of the next step.  Because I can't do another FOR loop for each individual column, I need to either summarize the values of all these columns and if null then delete...or something else?

Here is an example of my table, in this case only the 3rd line of date would qualify to be deleted. 

Entity2X_Clean3X_Clean4X_Clean
A136
B 45
A   
B413
A344
6110

 

Here is how I would have done it if I could hard code specific columns, except I can't because they vary.

dt << Select Where(Is Missing(:Name( "2X_Clean" ) | :Name( "3X_Clean" ) | :Name( "4X_Clean" )))<<Delete Rows;

Any ideas?  Thanks!

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How do I delete rows of variable column numbers when all are missing data

All you have to do is to build a literal string variable that is the dynamic code you need to run to accomplish what you want, and then once built, execute it.  The code below is a sample of how you would do it

theExpr = "dt << select where(isMissing(Sum(:" || col_list[1];
For( i = 2, i <= N Items( col_list ), i++,
	theExpr = theExpr || ",:" || col_list[i]
);
theExpr = theExpr || "))==1)<<delete Rows;";
Eval( Parse( theExpr ) );
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: How do I delete rows of variable column numbers when all are missing data

All you have to do is to build a literal string variable that is the dynamic code you need to run to accomplish what you want, and then once built, execute it.  The code below is a sample of how you would do it

theExpr = "dt << select where(isMissing(Sum(:" || col_list[1];
For( i = 2, i <= N Items( col_list ), i++,
	theExpr = theExpr || ",:" || col_list[i]
);
theExpr = theExpr || "))==1)<<delete Rows;";
Eval( Parse( theExpr ) );
Jim

View solution in original post

GoodMan
Community Trekker

Re: How do I delete rows of variable column numbers when all are missing data

Jim, this is a interesting topic. When i open the data table, as attached, and run the following jsl, it seems i can not delete the all null row. Why?

 

 

dt=current datatable();

theExpr = "dt << select where(isMissing(Sum(:" || col_list[1];

For( i = 2, i <= N Items( col_list ), i++,

theExpr = theExpr || ",:" || col_list[i]

);

theExpr = theExpr || "))==1)<<delete Rows;";

Eval( Parse( theExpr ) );

 

0 Kudos
txnelson
Super User

Re: How do I delete rows of variable column numbers when all are missing data

This is my fault.  I should have realized that a column starting with a number would confuse JMP.  I have added in :Name() functions around the references to the column names.

 

dt = Current Data Table();

col_list = dt << get column names( string );
For( i = N Items( col_list ), i > 0, i--,
	If( !Contains( col_list[i], "X_Clean" ),
		Remove From( col_list, i )
	)
);

theExpr = "dt << select where(isMissing(Sum(:name(\!"" || col_list[1];

For( i = 2, i <= N Items( col_list ), i++, 

	theExpr = theExpr || "\!"),:name(\!"" || col_list[i]

);

theExpr = theExpr || "\!")))==1)<<delete Rows;";

Eval( Parse( theExpr ) );
Jim
GoodMan
Community Trekker

Re: How do I delete rows of variable column numbers when all are missing data

Thanks, Jim.

0 Kudos