Subscribe Bookmark RSS Feed

JSL Selecting Columns Using Strings

sophiaw

Community Trekker

Joined:

Jul 10, 2015

Hi,

I'm having a problem where I have so many columns I want to select, but I don't want to type all of them out to create a new subset of them.

What I have is 200 columns that all start with the same string. I was wondering if there is a way I can select these columns with a loop or some command.

I have tried using the line:

cols = dt << Get Column Names(Starts With("Year"));

But if just gets every column name, and I do have a few columns that do not start with "Year" or even have "Year" in the name. Right now I'm making a new subset of the columns I want with:

dt2 = dt << Subset(

                            All rows,

                            columns(

                                          :Time,

                                          :Day,

                                          :Name("Year 1"),

                                          :Name("Year 2"),

                                          :Name("Year 3"),

                                          .

                                          .

                                          .

                                          etc.

                                          ),

                                          output table name("All Years")

);

There are many other columns in my table, but I just want to separate out these. Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

This code will do the trick.  Nice blog Dave, btw!

dt = New Table( "Year Example",

     Add Rows( 3 ),

     New Column( "ABC", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),

     New Column( "DEF", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 5, 6] ) ),

     New Column( "Year 1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [7, 8, 9] ) ),

     New Column( "Year 2", Numeric, Continuous, Format( "Best", 12 ), Set Values( [10, 11, 12] ) ),

     New Column( "Year 3", Numeric, Continuous, Format( "Best", 12 ), Set Values( [13, 14, 15] ) )

);

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

for (i = nitems(col_list), i > 0, i--,

     if (!contains(col_list[i], "Year"),

           remove from(col_list, i);

     );

);

dt << Subset(

     Output Table( "Year Subset" ),

     All rows,

     columns( eval(col_list) )

);

3 REPLIES
David_Burnham

Super User

Joined:

Jul 13, 2011

Solution

This code will do the trick.  Nice blog Dave, btw!

dt = New Table( "Year Example",

     Add Rows( 3 ),

     New Column( "ABC", Numeric, Continuous, Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),

     New Column( "DEF", Numeric, Continuous, Format( "Best", 12 ), Set Values( [4, 5, 6] ) ),

     New Column( "Year 1", Numeric, Continuous, Format( "Best", 12 ), Set Values( [7, 8, 9] ) ),

     New Column( "Year 2", Numeric, Continuous, Format( "Best", 12 ), Set Values( [10, 11, 12] ) ),

     New Column( "Year 3", Numeric, Continuous, Format( "Best", 12 ), Set Values( [13, 14, 15] ) )

);

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

for (i = nitems(col_list), i > 0, i--,

     if (!contains(col_list[i], "Year"),

           remove from(col_list, i);

     );

);

dt << Subset(

     Output Table( "Year Subset" ),

     All rows,

     columns( eval(col_list) )

);

sophiaw

Community Trekker

Joined:

Jul 10, 2015

Thank you PMroz, that worked great!

And that is a nice blog Dave, thanks for the link!