5

i have two one-to-may related layers (relation name: tree_observation) parent: trees and child: obeservations. The trees should be revisited on a regular base and for each observation a new child feature should be created. I set up the child layer with fields "treeID" (=connecting field), "Date" and "Employee" - date auto fills with the current date, employee is filled by the user doing the observation.

In the parent layer, i want to create a virtual field "last_observation_done_by" where the employee name who did the last observation should appear.

This is causing me trouble. I set up the virtual field this way:

aggregate(layer:='observations',aggregate:='concatenate',expression:="Employee",filter:=maximum("Date"))

-> in my understanding, this should only give the name of the employee of the latest observation, but only returns NULL

Another approach was to work with relation_aggregate:

relation_aggregate('tree_observation','concatenate',"Employee") 

but it seems to lack a filter function. Is there any other way to get the employee name of the latest observation from the child layer into my parent layer?

2
  • You state the table names are trees and observations, but the sample code is passing observation (singular, not plural). Is the sample code correct or your text description? What field(s) relate the parent and child tables?
    – bixb0012
    Commented Jan 8, 2023 at 19:53
  • 1
    thans for pointing that out - it was just a spelling mistake here in the sample code. In both layers, there is "treeID" which is the relating field.
    – sn1ks
    Commented Jan 9, 2023 at 7:22

2 Answers 2

3

The filter needs to return a boolean. Therefore you need to add a comparison:

filter:="Date"=maximum("Date")

Additionally I figured out a little strange behavior. In my testcase maximum("Date") returns a DateTime format, not a Date format, even if the field is in Date format. So you may need to cast it to a Date via to_date(maximum("Date")) to return the correct value which will evaluate to true in comparison with the field "Date".

Try:

aggregate(  
   layer:='observation',
   aggregate:='concatenate',
   expression:="employee",
   filter:="Date"=to_date(maximum("Date"))
)
3
  • that sound quite logical! unfortunately, it does not work with my dataset. It works, if i run the filter "Date"=to_date(maximum("Date")) on a new temporary field of my child layer - it's giving me "1" if it is indeed the newest observation and "0" if not. If I use the exact expression you posted, it again evaluates to NULL
    – sn1ks
    Commented Jan 4, 2023 at 12:24
  • 1
    new test with filter:=to_date(Now())=to_date("Date") gives my all results of todays observations - so it must be something with the maximum("Date")..
    – sn1ks
    Commented Jan 4, 2023 at 12:36
  • well, i tried with a completely new dataset anything thinkable - i come to the conclusion, maximum() does not work as filter. I tried it also with non-date fields (e.g. maximum("fid")=14 as 14 is the highest "fid" so far with the same empty result.
    – sn1ks
    Commented Jan 5, 2023 at 7:03
0

I think there are two questions to be addressed here. The first is can this be done, and the second is whether it should be done. To answer the first question, the following approach has worked for me in similar situations:

attribute(
  get_feature(
    'observations',
    map(
      'treeID', attribute('treeID'),
      'Date', to_date(aggregate(
        'observations',
        'max',
        "Date",
        "treeID"=attribute(@parent, 'treeID')
      ))
    )
  ),
  'Employee'
)

Similar to MrXsquared, I have noticed that aggregate returns Date values as DateTime values, so recasting using to_date allows for comparing Date values between parent and child.

The code above has worked for me, but I have also found its performance scales terribly with data set size, think of SQL CROSS JOIN with no indexes type of situation. So if the code works for you, great, but be forewarned you will notice its performance drop as the observations table grows.

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.