Automatic Grouping Script

abdulj

Hello All

I need some help on what the best way would be to group multiple columns of the same table.

My table would have columns similar to the following:

Z 3 , X 1, Z 2, X 3, X 4, Y 1, X 2, Y 2, Z 1

I would like to group all the Xs, Ys and Zs together and in numeric order if possible. This is a condensed version of what I deal with which is usually on the order of hundreds of columns with varying group sizes required.

The easiest way I think would be to create a list based on the column names, then use the loc() feature to return the position of each similar column name. However, what would be the best way to use the group() command?

Any help on this problem is appreciated.

thanks!

ms

There are probably several ways to do this. Here is one idea that should work for any table where grouping is to be based on the first letter of the column names.

// Example table

names = {Z 3, X 1, Z 2, X 3, X 4, Y 1, X 2, Y 2, Z 1};

dt = New Table( "test" );

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

  dt << New Column( Char( names[i] ), numeric )

);

// sort table columns and make list of first letter of column names

dt << reorder by name();

collist = dt << get column names( string );

For( i = 1, i <= N Col( dt ), i++,

  collist[i] = Left( collist[i], 1 )

);

// Make list of unique column name 1st letter

col_type = Associative Array( collist ) << get keys;

// Group columns

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

  pos = Loc( collist, col_type[i] );

  dt << group columns( Column( pos[1] ), N Row( pos ) );

);