Dynamic column name in expression builder

Ask the Community
1

Hi everyone,

I need some help to figure out how to insert a variable column name in an expression, and that expression to RETURN as expected.
My case:
with attribute(‘rt_id’) = ‘UCM000010’ – I obtain an unique ID value stored in column “rt_id” for any current_feature within main.table
lv.table contain 1 column for each feature from main.table, with name as feature.rt_id to which is referring to.
I try to retrieve information from dedicated column(along others) within lv.table for each feature within main.table.
If I write the expression as:

aggregate(

layer := 'lv',
aggregate := 'concatenate',
expression := concat("code", ' - ', "UCM000010", ' ', "um"),
filter := "UCM000010" IS NOT NULL
concatenator := '\n'

)
I have the correct RETURN as:
‘10010640 - 1 stk
10010413 - 10.99 lfm
10028103 - 10.99 lfm’

When I try to insert that “column_name” dynamic as:

aggregate(

layer := 'lv',
aggregate := 'concatenate',
expression := concat("code", ' - ', attribute('rt_id'), ' ', "um"),
concatenator := '\n'

)
expression don’t identify what column_name was referring to, RETURN only “code” & “um”, but for ALL rows & not values from specific column(with “filter” excluded because the RETURN was empty IF no column identified correctly):
'10010640 - stk
10010713 - stk
10010776 - lfm
…
So, clearly, expression don’t take that feature.rt_id correct in consideration and RETURN without information from that column within lv.table. I try also with “current_feature” OR “current_value”, same wrong result.
How should I pass correctly that feature attribute as column name in builder expression as the RETURN to be as expected?
Thank you in advance for your time and help!

Imported from GitHub discussion by @NeaCri on 2024-04-17T07:23:01Z

2
  1. Does this work in QGis ? If not, it’s not a QField issue
  2. As I understand (maybe bad), you’re referring to 2 tables. In your expression, where is the table reference ? Have you set a relation between tables ? By setting a relation (joining), you could make appear your 2nd table’s attribute in your first one and be able to make your expression work…

Imported from GitHub comment by @VxTedxV on 2024-04-17T15:50:17Z

3
  1. Yes, in qGIS I try to create a virtual column(or create new field and set DEFAULT value), on each of 5 main.spatial.tables) where to spell out those existing information from a separate non-spatial.lv.table(which contain 1 column for each feature within main.tables) within the project layers.
  2. I do not have JOIN those tables until now and I dont think this is the solution because the “Joined field” is different for each feature within main.tables(in lv. table are around 800 columns) and is no other connection between tables on fields like “id” or “code”, except…

The lv.table is created in PostgreSQL combining an existing(services).table (which contain around 200 unique “code” rows) with dynamic_columns for all features(unique rt_id.column_name), so, the only connection between lv.table AND any of main.table is that feature.rt_id from main.table which is a column_name within lv.table. Then, if I cannot pass that feature.rt_id in aggregate, is not any other way to connect those tables and retrieve aimed information with JOIN or Relation.
I try to avoid to write this simple logic in server-side, PostgreSQL, aiming only for a virtual column for each layer in front-end(qGIS) with that “aggregate” formula based on each feature.rt_id, but if I don’t find a solution on this issue, probably it will be next step.
more testing(new field):
With: expression := concat(“code”, ’ - ', “qty”, ’ ', “um”) I retrieve ‘qty’ column which is sum of all 800 other columns, no errors.
I need to change that “qty” with specific column_name = feature.rt_id(on DEFAULT for new field), I retrieve just an empty list with attribute(‘rt_id’). If I write attribute(‘rt_id’) in DEFAULT for new field, I retrieve as expected, the feature.rt_id.
But within the expression in aggregate formula is not recognized as a column_name from interrogated table.

I will want to thank you for your attempt!

Imported from GitHub comment by @NeaCri on 2024-04-17T21:01:12Z