topic Re: JSL Selecting Columns Using Strings in Discussions

JSL Selecting Columns Using Strings

sophiaw — Thu, 31 Aug 2017 09:39:20 GMT

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!

Re: JSL Selecting Columns Using Strings

David_Burnham — Wed, 15 Jul 2015 11:53:37 GMT

Not sure, but this might help:

http://www.pega-analytics.co.uk/blog/handling-lists-of-columns/

Re: JSL Selecting Columns Using Strings

pmroz — Thu, 31 Aug 2017 09:40:06 GMT

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

Re: JSL Selecting Columns Using Strings

sophiaw — Wed, 15 Jul 2015 16:10:05 GMT

Thank you PMroz, that worked great!

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