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

Question

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!,

DS

txnelson · Accepted Answer

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

jthi · Accepted Answer

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