482482_en-US

liliconcom

Recode multiple columns with list

I want to recode multiple columns using a conditional formula by going through each column with a For Each function. I am not able to recode successfully with the following code:

 

<

//Create list of columns to be recoded 

ColNames = {dt:Name("col 1"), dt:Name("col 2"),

 dt:Name("col 3"), dt:Name("col 4")};

 

 For Each({listitem}, ColNames, Recode(

listitem,

{Lowercase( _rcNow ), If( Contains( _rcNow, "too much" ),

"1000",

_rcNow

), Regex( _rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE )}

));

>

My hunch is that the body of the For Each clause is incorrect -- that I have to somehow call out the columns more clearly.

 

The data table as "dt" is established earlier in the code. For the record, I don't get an error when I use the script but the columns are not recoded properly. Thanks.

ScriptingRe: Recode multiple columns with list

Maybe you just need to use Recode Column instead of Recode and then tinker a bit on how to reference on columns.

This seems to be working, at least it replaces too much with 1000 in col 1:

Names Default To Here(1);

dt = New Table("Untitled 2",
 Add Rows(1),
 New Column("col 1", Character, "Nominal", Set Values({"too much"})),
 New Column("col 2", Character, "Nominal", Set Values({"a"})),
 New Column("col 3", Character, "Nominal", Set Values({"c"})),
 New Column("col 4", Character, "Nominal", Set Values({"d"}))
);

    //Create list of columns to be recoded 
ColNames = {"col 1"};
For Each({listitem}, ColNames,
 dt << Recode Column(
  //As Column(dt, listitem),
  AsColumn(listitem),
  {Lowercase(_rcNow), If(Contains(_rcNow, "too much"),
   "1000",
   _rcNow
  ), Regex(_rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE)},
  Update Properties(1),
  Target Column(Column(listitem))
 )
);

Re: Recode multiple columns with list

I adapted the code here for my purposes (several columns in the list) but it still does not work:

 

ColNames = {dt:Name("col 1"), dt:Name("col 2"),
 dt:Name("col 3"), dt:Name("col4")};
 
 For Each({listitem}, ColNames, 
 dt<<RECODE column=""></RECODE>

Were you suggesting I go column by column to recode? 

Re: Recode multiple columns with list

I'm not sure which formats for columns Recode Columns requires. You can see that we are using different syntaxes for ColNames (I'm using list of column names, and you are using old way to reference columns (Name("col name"), it has been replaced by "col name"n), also we are using different columns in Target Column.

If run the example I gave with this data table:

I end up with this

 

Here is updated example with new table and more columns added to ColNames list

Names Default To Here(1);

dt = New Table("Untitled 2",
 Add Rows(3),
 Compress File When Saved(1),
 New Column("col 1", Character, "Nominal", Set Values({"1000", "too much", "too much"})),
 New Column("col 2",
  Character,
  "Nominal",
  Set Values({"too much", "", ""}),
  Set Display Width(65)
 ),
 New Column("col 3", Character, "Nominal", Set Values({"c", "", "too much"})),
 New Column("col 4", Character, "Nominal", Set Values({"d", "", ""}))
);
wait(1);
    //Create list of columns to be recoded 
ColNames = {"col 1", "col 2", "col 3", "col 4"};
For Each({listitem}, ColNames,
 dt << Recode Column(
  //As Column(dt, listitem),
  As Column(listitem),
  {Lowercase(_rcNow), If(Contains(_rcNow, "too much"),
   "1000",
   _rcNow
  ), Regex(_rcNow, "\D*(\d+\.?\d*)\D*", "\1", GLOBALREPLACE)},
  Update Properties(1),
  Target Column(Column(listitem))
 )
);

 

Re: Recode multiple columns with list

Thanks for your help! Jordan Hiller helped me understand your original reply. I don't understand why using the list of columns as strings works while using a list of actual columns does not work, but I follow your solution now anyway. Helpful to see how column names are called out now too. Good reminder. Thanks again.