Counting substring across multiple fields in QGIS

concatenationcountexpressionfields-attributesqgis

In QGIS 3.18 I have several fields of concatenated elements (comma-separated), all of which have the possible substring Fort as either one discrete comma-separated element, OR a part of a comma-separated element

For example: the substrings Fort, Fort (Dore), or Fort (Ance).

I want to count the instances of the term 'Fort' across all strings and across all columns, regardless of what precedes or follows it in that substring element. For example:

Field A Field B Desired Output
Fort,Faible Moyen,Fort 2
Fort (Dore) NULL 1
Moyen Fort (Ance) 1
Fort Fort (Dore) 2

I know how to do this in 3 parts, but this would require making 3 columns and I would prefer to have it done in 1 single column. Here is what I can do:

First part: create one single string of all fields concatenated. ("Field C")

Field A ||','|| Field B

Second part: replace all instances of what I want to count with one single item so that I can count it in an array. ("Field D")

replace(replace("Field C",'Fort (Ance)','Fort'),'Fort (Dore)','Fort')

Third part: count all instances of 'Fort'. ("Field E")

array_length(
    array_filter(
        string_to_array("Field D"),
        @element='Fort'
    )
)

I attempted to use only the third part on my initial sequence, but as far as I can tell @element refers exclusively to entire values in the array (between commas) and I could not get it to recognize any element with Fort in it.

I want to put these three expressions in a sequence, as the expression for one single column… if this is possible.

Is it possible to create one single expression that contains these three expressions in sequence (first task 1, then 2, then 3)? I would like a non-Python solution, but would prefer a Python solution to no solution.

Best Answer

You can use:

array_length(
    array_filter(
        map_avals(
            attributes($currentfeature)
            ),
        @element like '%Fort%'
        )
    )

Explanation:

There is a handy array_count() expression, but it does not accept a LIKE match, only exact matches. That is why you need to filter the array for element which contain that word and then simply measure the length of the remaining array.

attributes() does return a dictionary of all field names and field values of the $currentfeature. To turn this into an array of all field values, you can use map_avals().

So if you do not want to consider all fields, you can use the following expression and specify the wanted fields by hand inside the array:

array_length(
    array_filter(
        array("Field A","Field B"),
        @element like '%Fort%')
    )
Related Question