topic Re: Column formula: How to correctly combine character columns that have missing entries? in Discussions

Column formula: How to correctly combine character columns that have missing entries?

SDF1 — Wed, 04 Oct 2023 18:30:30 GMT

Hi All,

Not sure why I'm having a hard time getting the correct solution to this, but I'm having a hard time combining columns of text when each column has missing entries that are not on the same row -- but all rows are aligned. See the screenshot below.

For example, if a row has an entry A or B or C, then if it's within a column, say :C1 or :C2, then the row is the same for both columns. For my data table, I have many text columns, each of them has some missing row entries, see above example. I am trying to use a column formula that uses all the different text entries of :C1 or :C2, to :CN.

I tried using the following column formula, but it doesn't fill in the column when :C1 Is Empty

I thought the solution would be pretty straightforward, but I'm not finding one, and drawing a blank on how to fix this. The nice thing about this is that for columns :C1 to :CN that I have in my data table, if a row is not empty, then all columns will have the same character for that row. For example, all columns have The entry D for row 4 (unless it's missing). I'd like to end up with a column that has no missing values by using the non-missing entries for each of my :CN columns, like the column above :Correct.

Hope this makes sense. Any help is much appreciated.

Thanks!,

Re: Column formula: How to correctly combine character columns that have missing entries?

txnelson — Wed, 04 Oct 2023 18:53:46 GMT

Is Empty() checks to see if the cell exists. You need to to Is Missing()

Re: Column formula: How to correctly combine character columns that have missing entries?

jthi — Wed, 04 Oct 2023 18:54:45 GMT

I'm not sure how fast it would be, but you could use Associative Array to get rid of all the duplicates. This isn't the cleanest solution but it might work

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"A", "B", "C", ""})),
	New Column("Column 2", Character, "Nominal", Set Values({"A", "", "", ""})),
	New Column("Column 3", Character, "Nominal", Set Values({"A", "", "C", ""}))
);

dt << New Column("Col", Character, Nominal, Formula(
	aa = Associative Array(Eval List({:Column 1, :Column 2, :Column 3}));
	(aa << get keys)[N Items(aa)];
));

Other option which should work fairly nicely is to find first value which isn't missing and then use that (build a list, use loc to find non-missing index, if one is found use it to get value from list and if not set it as missing)

dt << New Column("Col2", Character, Nominal, Formula(
	l = Eval List({:Column 1, :Column 2, :Column 3});
	ok_idx = Loc(!Matrix(IsMissing(l)));
	If(N Items(ok_idx) > 0,
		l[ok_idx][1];
	,
		""
	)
));

You can also Concatenate With Space

And then create other column which is First Word

Similar thing can be done with Cols/Utilities/Combine Columns and First Word column formula

Re: Column formula: How to correctly combine character columns that have missing entries?

SDF1 — Wed, 04 Oct 2023 19:06:24 GMT

Hi @txnelson and @jthi ,

Thanks for your quick responses. I decided to go with @txnelson 's solution -- it was nice and easy. I had it in my head that the conditional Is Missing() only worked on numbers, so I didn't even try that. I knew it should have been relatively simple.

Thanks!,

Re: Column formula: How to correctly combine character columns that have missing entries?

hogi — Thu, 05 Oct 2023 19:29:11 GMT

There is a wish to add some aggregation functions for Summary:

so, aggregation over many rows, grouped by other columns.

Perhaps it's worth to add such a character aggregation functionality as well as formula column for multiple columns?