- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Script concatenate columns
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)
);
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- 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), " "))
);
));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- 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), " "))
);
));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 !!!!