topic Re: Concatenate values in a column in Discussions

Concatenate values in a column

Anonymous — Thu, 19 Jul 2018 14:27:59 GMT

Hello all,

I am trying to create a new column that concatenates column A, B and C together. The example and the script is showing bellow. The script doesn't work. could someone help me with it?

Thanks,

A B C

dt_summary << new column("Category", string,
formula(:Name( "Frame Config" ) || "_" ||:Name( "Bar Spacing (mm)" )||"_"||:Name( "Clamping Spacing (mm)" )),
eval formula);

Re: Concatenate values in a column

Mark_Bailey — Mon, 09 Jul 2018 17:26:32 GMT

dt_summary << New Column( "Category",
	Character,
	Formula(
		:Name( "Frame Config" ) || "_" || :Name( "Bar Spacing (mm)" ) || "_" ||
		:Name( "Clamping Spacing (mm)" )
	)
);

The data type is "character" not "string".

Re: Concatenate values in a column

Anonymous — Mon, 09 Jul 2018 17:25:29 GMT

Thank you so much for your quick response!

There is an error message showing: Cannot stack. Stacked columns are of different data type

Re: Concatenate values in a column

Anonymous — Mon, 09 Jul 2018 17:28:37 GMT

I am wondering if there is anyway that allow me show something like "3-bar-1000-400" in my new column.

Thanks a lot,

Winnie

Re: Concatenate values in a column

Anonymous — Mon, 09 Jul 2018 17:41:39 GMT

Hi Mark,

Thank you so much again. I have changed script based on your deriction, but the new column is empty. I'm wondering if I can concatenate different data type in jmp?

Thanks,

Winnie

Re: Concatenate values in a column

cwillden — Mon, 09 Jul 2018 17:52:34 GMT

I'm not sure about the "stacking" stuff in the error since you're not stacking columns here, but I would expect an error from concatenating numeric values without converting them to character strings first. Wrap each numeric column in your formula in char().

dt_summary << New Column( "Category",
	Character,
	Formula(
		:Name( "Frame Config" ) || "_" || char(:Name( "Bar Spacing (mm)" )) || "_" ||
		char(:Name( "Clamping Spacing (mm)") )
	)
);

Re: Concatenate values in a column

dale_lehman — Mon, 09 Jul 2018 17:54:37 GMT

I did it with a formula, but you should be able to script it from this. Yes, the columns must be character types to concatenate, and to insert the - you surround it in quotes:

:Column 1 || "-" || Char( :Column 2 ) || "-" || Char( :Column 3 )

Re: Concatenate values in a column

Mark_Bailey — Mon, 09 Jul 2018 18:01:11 GMT

I apologize for my post that suggested stacking. I misunderstood your request. I wan't fast enough at replacing it with a solution.

Re: Concatenate values in a column

Anonymous — Mon, 09 Jul 2018 19:08:23 GMT

Thank you so much for everyone's help. My issue has been sloved.