3

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.

2
  • I am not sure why my table formatting is not working in the post -- it works fine when I preview it. Any ideas? Until I can figure it out, I've posted a screenshot of it.
    – pete
    Nov 22, 2021 at 19:12
  • This was my table attempt: | Field A | Field B | Desired Output | | -------- | -------------- | -------------- | | Fort,Faible | Moyen,Fort |2 | Fort (Dore) | NULL |1 | Moyen | Fort (Ance) |1 | Fort | Fort (Dore) |2
    – pete
    Nov 22, 2021 at 19:16

1 Answer 1

3

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%')
    )
1
  • Excellent. I am loving the array functions more and more. Just added array_filter to my toolkit.
    – pete
    Nov 22, 2021 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.