Concat fields which are not NULL

Question

I am using the concat function in QGIS 3.16.6 on Mac.
I want to  join several cell values into one new cell.
With the concat function it is ignoring cells with NULL but it doesn't separate the cell values. If i'm doing the separator it is not connected to the NULL cell value.
I also tried to do it with the coalesce but with the same result
concat("A"+',',"B"+',',"C"+',',"D")
coalesce("B" ,'(nodata)')|| ',' || 
coalesce( "C" ,'(nodata)')|| ',' || 
coalesce("D" ,'(nodata)'))

With this function I still get an separator eventhought there is no value in the cell. The separator should be linked if there is a value or not.
Hear is a picture of my data. In the column Join is want i always get as result and the column wanted is how it should look like

MrXsquared · Answer

For this usecase I suggest to use arrays together with maps, which is probably the simplest method to get your result:
array_to_string(array_filter(map_avals(attributes($currentfeature)),@element != ','),',')

Explanation: First you get all attributes and their values of the current feature by using attributes($currentfeature). This returns a map of all keys (fieldnames) and values (attribute values). By using map_avals() we will only keep all the values as an array. On this array we a filter to sort out the separators (@element != ','). So at this point we have an array of all values of the current feature, not beeing NULL. To turn this into a string, we use array_to_string() with the ',' as separator.
If you dont want all fields, but only some specific, you can use this expression:
array_to_string(array_filter(array("A","B","C","D"),@element is not null),',')

and specify the fieldnames in the array(). Like array("A","C") for example to only use the fields A and C.

Previous answer before the additional details:
Instead of + use , to concat the values with concat() function, like this:
concat("A",',',"B",',',"C",',',"D")

Concat fields which are not NULL

One Answer

Add your own answers!

Ask a Question