3

I have two tables, the table 'fruit' contains a list of fruits (see figure 01).

enter image description here

The 'municipality' table shows in the column "execucao01" which fruits exist in each municipality (see figure 02).

enter image description here

I would like to create a virtual field in the 'fruit' table to obtain the total existence of each type of fruit in the 'municipality' table.

I tried to use the aggregation function.

count(
    aggregate(layer:='municipality',
    aggregate:='concatenate',
    expression:="execucao01"),
    filter:=(
        array_contains(
            string_to_array(
                substr(
                    aggregate(layer:='municipality',
                    aggregate:='concatenate',
                    expression: = "execucao01"), 2, -1)
                ),
            ' "FRUIT" ')
        ) = true
    )
1
4

This is a a "Virtual Layer" approach (not a virtual field solution), but hopefully you get what you have aimed.

Assuming you already have a table below, following the answer to your earlier question.

enter image description here

Then I propose to turn the data table into the intended format (so-called 'pivot table') by:

  1. From the menu, select Layer > Create Layer > New Virtual Layer
  2. Import the layer (data layer in this example).
  3. Enter a query as described below.
SELECT *
FROM(
      SELECT 'BANANA'  AS FRUIT, Max(BANANA)  AS TOTAL FROM data
UNION SELECT 'MACA'    AS FRUIT, Max(MACA)    AS TOTAL FROM data
UNION SELECT 'PERA'    AS FRUIT, Max(PERA)    AS TOTAL FROM data
UNION SELECT 'LARANJA' AS FRUIT, Max(LARANJA) AS TOTAL FROM data
)

enter image description here

Then you will get a new virtual_layer whose attribute table will show the summary.

enter image description here

3

Here is a more or less simple expression that does the job (explanation as comment in expression), assuming your execucao01 field is a string field:

array_length(
    array_filter(
        string_to_array(
            array_to_string(
                aggregate('municipality','array_agg',regexp_replace("execucao01",'[{}"]','')) -- aggregate all values from the destination layer into one single array; remove {, } and " signs from the values
            ,',') -- array_to_string: turn the array into a string, as it has the wrong format
        ,',') -- string_to_array: create a "real" array from the string
    ,@element = "FRUIT") -- array_filter: only keep the lookup-fruit in the current array
) -- array_length: count number of fruits

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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