topic Re: Script concatenate columns in Discussions

Script concatenate columns

Dennisbur — Thu, 08 Jun 2023 16:39:48 GMT

Hello

I have written a script for concatenating columns in column "PIN_NAME"

My problem is that I don't have a constant amount of columns.

I should concatenate column from Test_name 10 until Test_name 15, but some times I have just Test_name 10 until Test_name 13 (for example)

and I received an error because the script tries to concatenate Test_Name 14, and Test_Name 15 which don't exist.

Can you assist me with this issue?

Column(dt_split,"PIN_NAME") << Formula(Char(:TEST_NAME 10 || " " ||
:TEST_NAME 11 || " " ||
:TEST_NAME 12 || " " ||
:TEST_NAME 13 || " " ||
:TEST_NAME 14 || " " ||
:TEST_NAME 15)
);

Re: Script concatenate columns

jthi — Mon, 30 Jan 2023 17:54:18 GMT

This requires that the columns of interest are character columns, you have JMP16 and that the columns pass regex "Column \d{2}"

General idea

Get list of column names
Build a list of columns names you are interested in by using some filter (I'm using regex)
Build expression for the formula with the list from 2.
Use Eval(EvalExpr()) to add it to new column with a formula

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(0),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"a"})),
	New Column("Column 2", Character, "Nominal", Set Values({"b"})),
	New Column("Column 10", Character, "Nominal", Set Values({"c"})),
	New Column("Column 11", Character, "Nominal", Set Values({"d"})),
	New Column("Column 12", Character, "Nominal", Set Values({"e"}))
);

col_names = dt << Get Column Names(Character, "String");

list_expr = Expr(List());
For Each({col_name}, col_names,
	If(!IsMissing(Regex(col_name, "Column \d{2}")),
		Insert Into(list_expr, Name Expr(AsColumn(Column(dt, col_name))));
	);
);

// show(Name Expr(list_expr));

Eval(EvalExpr(
	dt << New Column("PINNAME", Character, Nominal,
		Formula(Concat Items(Expr(list_expr), " "))
	);
));

Re: Script concatenate columns

hogi — Tue, 13 Aug 2024 17:14:35 GMT

If you have Jmp17, things will be much easier.

Then you can call a function from New Formula Column - no restriction to columns with type character :)

Data Table( "Big Class" ) << New Formula Column(
	Operation( Category( "Character" ), "Concatenate with Space" ),
	Columns( :name, :age, :sex, :height, :weight )
);

Even better: you can even use variants which are disabled in the right click New Formula Column menu.

like concatenation for numeric entries and counting of character column entries:

Data Table( "Big Class" ) << New Formula Column(
	Operation( Category( "Character" ), "Concatenate with Space" ),
	Columns( :height, :weight )
);

);
Data Table( "Big Class" ) << New Formula Column(
	Operation( Category( "Aggregate" ), "Count" ),
	Columns( :name ),
	Group By( :sex )
);

disabled in the right click New Formula Column menu - ARGH !!!!