371125_en-US

liliconcom

JSL Creating a New Column from Components in another Column Character Scripting

Suppose I have input column  and want to create a new column based it's contents. The input column will always have a total of 8 slashes like below. However, sometimes there will be actual data of varying lengths between them. Thus... I want to indicate in a new column when there is data with a simple 'Y' and all other places would be 'N' over a large number of rows. 

 

Input Column                  Desired_Output

////////                                NNNNNNNNN

////////                                NNNNNNNNN

////a/b///                            NNNNYYNNN

a////////                              YNNNNNNNN

Quality and Process EngineeringRe: JSL Creating a New Column from Components in another Column Character Scripting

Very big thank you! And to all others that helped. The Formula2 nailed it. 

 

 

Re: JSL Creating a New Column from Components in another Column Character Scripting

Howewer, to return 8 characters in each case you may use another formula, see formula2 in that table:

 

New Table( "Test 3",
 Add Rows( 4 ),
 New Column( "Input Column",
  Character,
  "Nominal",
  Set Values( {"////////", "////////", "////aaaa/b///", "aBCD////////"} )
 ),
 New Column( "Formula",
  Character,
  "Nominal",
  Formula(
   Regex(
    Regex(
     Regex( :Input Column, "[a-z]+", "A", GLOBALREPLACE, IGNORECASE ),
     "[^/]",
     "Y",
     GLOBALREPLACE
    ),
    "/",
    "N",
    GLOBALREPLACE
   )
  ),
  Set Display Width( 246 )
 ),
 New Column( "Formula2",
  Character,
  "None",
  Formula(
   item_lst = Words( Substitute( :Input Column, "/", "/;" ), ";" );
   result_str = "";
   For( i = 1, i <= N Items( item_lst ), i++,
    If( item_lst[i] == "/",
     result_str = result_str || "N",
     result_str = result_str || "Y"
    )
   );
   result_str;
  ),
  Set Selected,
  Set Display Width( 394 )
 )
)

Re: JSL Creating a New Column from Components in another Column Character Scripting

You can add just another regex, that replaces many characters by one:

 

Names Default To Here( 1 );

dt = New Table( "Test",
 Add Rows( 4 ),
 New Column( "Input Column", Character, "Nominal", Set Values( {"////////", "////////", "////aaaa/b///", "aBCD////////"} ) ),
 New Column( "Formula",
  Character,
  "Nominal",
  Formula( Regex( Regex( Regex( :Input Column, "[a-z]+", "A", GLOBALREPLACE, IGNORECASE ), "[^/]", "Y", GLOBALREPLACE ), "/", "N", GLOBALREPLACE ) ),
  Set Selected,
  Set Display Width( 246 )
 )
);

Re: JSL Creating a New Column from Components in another Column Character ScriptingThanks for the reply. Wasn't familiar with this function and it is useful/helpful. This takes care of the slashes but gives me a 'Y' for every letter in the string. I only want one 'Y' for all characters between the slashes.Re: JSL Creating a New Column from Components in another Column Character Scripting

You could use double regex in a formula:

Regex(Regex(:Input Column, "[^/]", "Y", GLOBALREPLACE), "/", "N", GLOBALREPLACE)

One regex to replace everything that isn't "/" with "Y" and other to replace "/" with "N".