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

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

fat_angus — Sat, 10 Jun 2023 23:27:46 GMT

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

////a/b/// NNNNYYNNN

a//////// YNNNNNNNN

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

jthi — Wed, 24 Mar 2021 19:02:25 GMT

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".

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

fat_angus — Wed, 24 Mar 2021 19:58:05 GMT

Thanks 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

Georg — Wed, 24 Mar 2021 21:30:27 GMT

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 Scripting

Georg — Wed, 24 Mar 2021 21:52:51 GMT

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

fat_angus — Wed, 24 Mar 2021 23:48:50 GMT

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