cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Get the free JMP Student Edition for qualified students and instructors at degree granting institutions.
Choose Language Hide Translation Bar
View Original Published Thread

Script concatenate columns

Dennisbur
Level IV

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?

 

Dennisbur_0-1675098460791.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User


Re: Script concatenate columns

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

 

General idea

  1. Get list of column names
  2. Build a list of columns names you are interested in by using some filter (I'm using regex)
  3. Build expression for the formula with the list from 2.
  4. 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), " "))
	);
));
-Jarmo

View solution in original post

2 REPLIES 2
jthi
Super User


Re: Script concatenate columns

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

 

General idea

  1. Get list of column names
  2. Build a list of columns names you are interested in by using some filter (I'm using regex)
  3. Build expression for the formula with the list from 2.
  4. 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), " "))
	);
));
-Jarmo
hogi
Level XII


Re: Script concatenate columns

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.
new formula column menu - don't disable variants 

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