Combine columns except when null (i.e., skip null)

tlammie1806

Hi there,

Is there a way to combine values in multiple columns except when the value is null? For example:

This is a small example, I need to combine 8 different columns with a separator without ending up with: cat, , , , , orange, .

I'm essentially looking for a DOMO equivalent of TEXTJOIN() in Excel. The values don't always inform each other i.e., the sixth column may not be null while the third column may be null.

Thank you in advance!

tlammie1806

My solution is kind of a work-around but the basic idea is to use a delimiter to combine the columns and remove them later on:

1. Add a comma (or another delimiter) to the end of each text column that need to be combined (e.g., cat --> cat,)
2. Combine the columns (will look something like cat,dog,,,bird,,)
3. Replace text LIKE ',,%' with ','
4. Replace ',' (or comma no space) with ', ' (or comma with space)
5. Remove last char

ST_Superman

you could also try a formula tile to combine:

CASE 
  WHEN `column A` IS NULL THEN `column B`
  WHEN `column B` IS NULL THEN `column A`
  ELSE CONCAT(`column A`, ', ', `column B`)
END

... Sorry, just re-read your entire post and I noticed that you are combining 8 fields.

CONCAT(CASE WHEN `column A` is NULL then '' else concat(`column A`,', ') end,
       CASE WHEN `column B` is NULL then '' else concat(`column B`,', ') end,
       CASE WHEN `column C` is NULL then '' else concat(`column C`,', ') end,
       CASE WHEN `column D` is NULL then '' else concat(`column D`,', ') end,
       CASE WHEN `column E` is NULL then '' else concat(`column E`,', ') end,
       CASE WHEN `column F` is NULL then '' else concat(`column F`,', ') end,
       CASE WHEN `column G` is NULL then '' else concat(`column G`,', ') end,
       CASE WHEN `column H` is NULL then '' else concat(`column H`,', ') end
       )

amehdad

Hi @tlammie1806, an option that could get it closer to your desired state is using CONCAT function, to combine data, as well as a RegEx (e.g. /s), to remove any blank/white spaces. Unfortunately, there isn't an equivalent of TEXTJOIN in Domo currently

tlammie1806

My solution is kind of a work-around but the basic idea is to use a delimiter to combine the columns and remove them later on:

1. Add a comma (or another delimiter) to the end of each text column that need to be combined (e.g., cat --> cat,)
2. Combine the columns (will look something like cat,dog,,,bird,,)
3. Replace text LIKE ',,%' with ','
4. Replace ',' (or comma no space) with ', ' (or comma with space)
5. Remove last char

ST_Superman

you could also try a formula tile to combine:

CASE 
  WHEN `column A` IS NULL THEN `column B`
  WHEN `column B` IS NULL THEN `column A`
  ELSE CONCAT(`column A`, ', ', `column B`)
END

... Sorry, just re-read your entire post and I noticed that you are combining 8 fields.

CONCAT(CASE WHEN `column A` is NULL then '' else concat(`column A`,', ') end,
       CASE WHEN `column B` is NULL then '' else concat(`column B`,', ') end,
       CASE WHEN `column C` is NULL then '' else concat(`column C`,', ') end,
       CASE WHEN `column D` is NULL then '' else concat(`column D`,', ') end,
       CASE WHEN `column E` is NULL then '' else concat(`column E`,', ') end,
       CASE WHEN `column F` is NULL then '' else concat(`column F`,', ') end,
       CASE WHEN `column G` is NULL then '' else concat(`column G`,', ') end,
       CASE WHEN `column H` is NULL then '' else concat(`column H`,', ') end
       )