Listing all columns that are not blank

1

I am trying to get a summary of columns in row without explicitly naming each column.
I want a column that is a list of the names of all the columns that are not blank.

Is this possible?

2

@anon28433972 - no it is not possible without explicitly listing the names.
However, when I see this request it looks like a red flag to check the schema of how the doc is setup.

Its is not the way most folks start designing their table but having rows instead of columns - so transposing the table usually helps. Take a look at Referencing a variable column header and see if it makes sense for you.

Also happy to look at your doc and offer suggestions!

3

Thanks @mallika,

I’ll give that a shot and see how I can manage it.

4

This doesn’t help Eric with his problem on the other link (sorry!), but here’s a refactored version of that sample problem. The issue is where something that should really be a value in the table is mistakenly made a column in the table instead. A classic example is a sales table where there are columns like “Jan_Sales”, “Feb_Sales” etc. when really the single sales row should be represented as 12 rows with an extra column representing the month itself.

In the example on the other link there are columns for shapes, a design decision which has (at least) two negatives:

  1. You can’t easily add more shapes without not only having to add new columns but changing all of your logic to take the new columns into account (this is I think the problem Eric is experiencing in his real application)
  2. You can’t easily work with the actual data because relational languages aren’t geared up for dealing with “variable” columns.

The refactored version can allow new shapes and colours to be added at any time (note that I used raw text for shape and colour names to keep it simple - in “real life” you would define lists or lookup tables instead). The logic that sums up the numbers into the top table is also much simplified - it goes from:

SwitchIf(thisRow.Shapes="Squares", sum([Table 1.2].Squares), thisRow.Shapes="Circles", sum([Table 1.2].Circles), thisRow.Shapes="Triangles", Sum([Table 1.2].Triangles))

To:

[Table 2.2].Filter(Shape=thisRow.Shapes).Number.Sum()

The new version also does not need to be amended as new colours or shapes are added, and Coda’s grouping helps you make the refactored version look very close to the original version by “rolling up” the additional rows that have been created.

Hope this helps. I wanted to write this because I’ve seen a lot of posts about designs involving split attributes in the last week or so. It’s an easy mistake to make but it makes your life really difficult. :wink:

2 Likes
5

Thanks a lot for this solution Nick.
Unfortunately not applicable in my case as I can’t refactor table 2.2 as you suggested. It would solve this problem but it would create another similar one eslewhere… :confused: