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

tlammie1806
tlammie1806 Member
edited October 2022 in Magic ETL

Hi there,

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

image.png

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!

Tagged:

Best Answers

  • tlammie1806
    tlammie1806 Member
    edited December 2022 Answer ✓

    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
    ST_Superman Domo Employee
    edited December 2022 Answer ✓

    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
       )


Answers

  • amehdad
    amehdad Coach

    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
    tlammie1806 Member
    edited December 2022 Answer ✓

    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
    ST_Superman Domo Employee
    edited December 2022 Answer ✓

    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
       )


Categories