JSL Selecting Columns Using Strings

sophiaw

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!

pmroz

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