Given data
I have a layer1 as output of a model that has a field kmh and a field kfzh. Both are integer fields.
kmh has one of the following values: NULL,0,7,10,20,30,40,50,60,70,80,90,100
kfzh has a value between 1 and 2000 or NULL. Values can exist multiple times, values are not in order, there can be skipped values.
I have a layer2 that is a table in a geopackage. It has the following fields: fid,0,7,10,20,30,40,50,60,70,80,90,100. fid is integer64 as per gpkg standards, all others are saved as string. It contains 2000 features.
Issue
In layer1, I want to create a virtual field, that reads the kmh and kfzh values and looks up a new value stored in the features of layer2.
Sample data and expected results
layer1
| ID | kmh | kfzh | expected virtual field output |
|---|---|---|---|
| 27 | 50 | 165 | 1 |
| 28 | 70 | 1053 | 3 |
| 29 | 30 | 457 | 1 |
layer2
| fid | 30 | 50 | 70 |
|---|---|---|---|
| 165 | 1 | 1 | 2 |
| 457 | 1 | 2 | 3 |
| 1053 | 2 | 3 | 3 |
Tested approaches
I only understand the basics of field calculator and expressions, so i started with a basic input and then let AI try to improve that (pls don't judge me on that!). These two approaches came out but both don't manage to grab my data from layer2. I suspect it has mostly to do with data formats and the fact that kfzh is a integer in layer1 and a string name field in layer2.
Approach 1
attribute( get_feature('layer2', 'fid', attribute(@parent, 'kfzh')), '[' || to_string(attribute(@parent, 'kmh')) || ']' ) Approach 2
CASE WHEN "kmh" IS NULL OR "kfzh" IS NULL THEN NULL ELSE CASE WHEN "kmh" = 0 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="0", filter:= "fid" = "kfzh" ) WHEN "kmh" = 7 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="7", filter:= "fid" = "kfzh" ) WHEN "kmh" = 10 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="10", filter:= "fid" = "kfzh" ) WHEN "kmh" = 20 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="20", filter:= "fid" = "kfzh" ) WHEN "kmh" = 30 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="30", filter:= "fid" = "kfzh" ) WHEN "kmh" = 40 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="40", filter:= "fid" = "kfzh" ) WHEN "kmh" = 50 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="50", filter:= "fid" = "kfzh" ) WHEN "kmh" = 60 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="60", filter:= "fid" = "kfzh" ) WHEN "kmh" = 70 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="70", filter:= "fid" = "kfzh" ) WHEN "kmh" = 80 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="80", filter:= "fid" = "kfzh" ) WHEN "kmh" = 90 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="90", filter:= "fid" = "kfzh" ) WHEN "kmh" = 100 THEN aggregate( layer:='layer2', aggregate:='array_max', expression:="100", filter:= "fid" = "kfzh" ) ELSE NULL END END Constraints
- I cannot change field data types
- I cannot use functions
- it has to be done in one virtual field only
Bigger goal
To help clarify und justify my approach: I am trying to automate a workflow as much as I can. This is a step where a model output will have some values set for kmh and kfzh, a user will add more data. I want to store the virtual field in a qml along with many others and other settings, that the user can load after manual editing. The current workflow takes the output data table into excel, performs multiple functions and then gets back into GIS via table join. I want to keep all data inside GIS and avoid Excel. Currently, the above problem is solved using an index for kmh and then a double vlookup to runthough the table. Even worse, I know.
