Deleting columns that are not in a list

[#1]

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.

[#2 · Accepted 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

[#3]

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

[#4]

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.

[#5]

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!

[#6]

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)
	)
);

[#7]

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 ) ); );

[#8]

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

[#9]

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

[#10]

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/