-1

I have a table of data like so.

Comment 1 Comment 2 Comment 3
This is a text comment null null
null this text comment has a number 2 null
null null this is comment 3

I want to create a calculated field such that I have a column that aggregates all the data into one like so.

Combined Comment
This is a text comment
this text comment has a number 2
this is comment 3

I did read through GDS help documents about CONCAT and I found that the null values make it so CONCAT comes out null.

I had slightly better luck with a CASE statement when combining two of the columns but not all three.

I also tried to combine the two columns then used that combined column into another calculated field without luck.

All fields are set to text, many have a mix of alpha and numeric characters but should be treated as text. None are only numbers.

Any help is appreciated.

Deca
  • 11
  • 5

1 Answers1

0

Create a new calculated field, with a nested function? The below worked on your example:

REPLACE(CONCAT(CONCAT(Comment1,Comment2),Comment3),'null','')

Lawrence Stroud
  • 3
  • 2
  • Hi Lawrence, thanks for the response. I'm still new to GDS and have overlooked the fact that the table chart I'm verifying data on is set by default to read blank values as null. My dataset however does not use the word null and has missing values. I did give your solution a go but ultimately I think that is why it didn't work for me. – Deca Jan 28 '21 at 07:59
  • The most obvious solution is to clean the data at source. So create a new field in your data source that cleans / concats and then bring that new field through to GDS. Possible? – Lawrence Stroud Jan 28 '21 at 15:55
  • Yes, I have thought of this as well - data is incoming from a google form. The form was made by someone else at the organization. Could create a column that combines everything very easily on the sheet its self and refresh it with a script. Just thinking that it may be easier to do in GDS but looks like not. Thank you for your input good to know I'm on the right track with thinking that. – Deca Jan 29 '21 at 01:01