topic Re: Concatenate columns with empty cells in Discussions

Concatenate columns with empty cells

JMP_fan — Sat, 10 Jun 2023 23:30:59 GMT

Hi all,

I have a dataset with different columns that have some empty random cells (like the example in the table below). I would like to concatenate the values of the different columns and use the "_" to separate the content of the cells only if the content of the cells is different from empty value (as in column "concat" below). Does anyone have a suggestion on a how to to this operation in a smart way?

Thanks for your help!

Name	Age	Sex	Concat
Mads	31		Mads_31
		M	M
John	25		John_25
Julia	19	F	Julia_19_F

Re: Concatenate columns with empty cells

Mark_Bailey — Wed, 02 Jun 2021 14:42:27 GMT

You should be able to use a simple function in a new column Concat:

Re: Concatenate columns with empty cells

txnelson — Wed, 02 Jun 2021 14:48:29 GMT

Here is one way of writing the formula

colList = {"Column 1", "Column 2", "Column 3"};
x = "";
For( i = 1, i <= N Items( colList ), i++,
	If( As Column( colList[i] ) != "",
		If( x != "", x = x || "_" );
		x = x || Char( As Column( colList[i] ) );
	)
);
Trim( x );

Re: Concatenate columns with empty cells

brady_brady — Thu, 03 Jun 2021 16:15:39 GMT

Hi, here is yet another formula.

Concat Items( Words( :Name || "," || :Age || "," || :Sex, "," ), "_" )

Cheers,

Brady