nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

fat_angus

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

Accepted answers

Georg

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

All comments

jthi

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

fat_angus

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.

Georg

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

Georg

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