topic Concatenation highlights of columns in Discussions

Concatenation highlights of columns

Liranlev — Tue, 18 Jun 2024 08:27:49 GMT

Hello there,

I have a table with some columns, for example, "HIGH" and "SMART," and there are three optional values: "0," "1," and "None."

I want to create a new column that describes each row.

For example, if the first row has a "1" in the "HIGH" and "SMART" columns, the new column should contain "HIGH, SMART."

How can I achieve this using column formulas?

Thank you.

Re: Concatenation highlights of columns

jthi — Tue, 18 Jun 2024 08:52:35 GMT

If you have a table like this

you can use formula

vals = {};
If(:H == "1", Insert Into(vals,"H"));
If(:S == "1", Insert Into(vals,"S"));

Concat Items(vals, ", ");

There are also many other methods of achieving this

Re: Concatenation highlights of columns

hogi — Tue, 18 Jun 2024 18:12:39 GMT

What you describe is almost the application case of Combine Columns with the setting Selected Columns are Indicator Columns.

But is will complain about the Nones.

To fix the issue, get rid of the None values - either via Recode or by just converting the columns to data type numeric:

dt = New Table( "test",
	Add Rows( 5 ),
	New Column( "H",
		Character,
		Set Values( {"None", "1", "0", "1", "1"} )
	),
	New Column( "S",
		Character,
		Set Values( {"1", "0", "1", "None", "1"} )
	)
);

:H << Set Data Type( Numeric );
:S << Set Data Type( Numeric );

dt << Combine Columns(
	columns( :H, :S ),
	Column Name( "summary" ),
	Delimiter( "," ),
	Selected Columns are Indicator Columns( 1 )
);