Subscribe Bookmark RSS Feed

Deleting columns that are not in a list

Hi Guys,

I have a set of columns that I want to keep and then delete all except this set.

The columns that I want to delete are large in number.

Is there any easy way to do this using a script?

Thanks.
1 ACCEPTED SOLUTION

Accepted Solutions
Solution
Loop through all columns to create a list of those Columns you wish to delete, by comparing each column with your list of keepers.

Example

10 REPLIES
Solution
Loop through all columns to create a list of those Columns you wish to delete, by comparing each column with your list of keepers.

Example

Thanks, that worked. I wish you could use two lists and subtract/ex or one from the other.
ms

Super User

Joined:

Jun 23, 2011

That might work if you have only numeric items (L-values). However that's rarely the case with column names. Maybe you could switch between names and numbers by using associative arrays in a clever way.
Von_Andre

Occasional Contributor

Joined:

May 11, 2017

Hello. I was browsing this discussion. Found the subject very helpful for me but I cannot see the solution. Must have been deleted or something? Appreciate if you can post it back. Thanks!
David_Burnham

Super User

Joined:

Jul 13, 2011

Slightly different bu the same idea, the following code deletes columns that are in a list.  Specifically you have a list of columns (all), and items are removed if they are contained in the list excl.

all = dt << Get Column Names(string);
// assume excl is a list of column names that i want to remove
For (i=1,i<=NItems(excl),i++,
	pos = Contains(all,excl[i]);
	If (pos>0,
		RemoveFrom(all,pos)
	)
);
-Dave
Von_Andre

Occasional Contributor

Joined:

May 11, 2017

Hi Dave, Appreciate your quick response on my query. I did your sample code but I seems the columns on my list still appear in my table. Is there something I missed? dt = Data Table( "temp" ); all = dt << Get Column Names( string ); vt = {"lot_id", "wf_id", "die_x"}; For( i = 1, i <= N Items( vt ), i++, pos = Contains( all, vt[i] ); If( pos > 0, Remove From( all, pos ) ); );
kevin_c_anderso

Community Trekker

Joined:

Jun 5, 2014

"There's a million ways to get things done..."  Talking Heads, "What A Day That Was", 1984

 

If the number of columns to be deleted is truly large, it might be more efficient to Subset the table with all rows, keeping only the columns in your list.  That could save lots of iterative deletion.

 

Your application might not be that easy, though.

Von_Andre

Occasional Contributor

Joined:

May 11, 2017

Thanks! Is it possible to subset columns that matches within my criteria?
I'm interested to subset only columns containing keywords. And not the full column names only (since I have a lot of data).
David_Burnham

Super User

Joined:

Jul 13, 2011

Sorry, maybe I misuunderstood.  The code removes items from the list not from the table.  But the updated list can be used with a Subset command to generate a subset table only with the columns you are interested in.

More here: http://www.pega-analytics.co.uk/blog/scripting-table-subsets/

 

-Dave