pipe / convert a string to fieldname in visualization

[fe-c]

Hi,

is it possible to 'pipe' or convert a string (dynamic concatenated based on selections) to a fieldname in my visualization/chart?

For example:
table: tab1
field: tab1_field1 (tab1_ is a prefix to prevent multiple linking between tables)

table: tab2
field: tab2_field1

…

Now I generate a variable by concatenating multiple selections:

selection: 'geometry_2'
variable: varTab = GetFieldSelections(…) & '_' & 'field1' = 'tab2_field1' (string)

Is it possible to use this string as input like avg($(varTab)) to get a result similar to avg(tab2_field1)?

[marcus_sommer]

In general it's possible to combine calculations over several fields/tables and also to synchronize their selections. But it's recommended to associate multiple fact-tables else creating a star-scheme data-model by merging all facts into a single table by harmonizing field-names and data-structures.

In your case it might be a rather simple concatenate of the tables by adding the source-information within an extra field to be able to differentiate between the sources per selection or set analysis.

[fe-c]

Hello Marcus, thanks for reply.

Sure it would be an option to concat the tables, but at the moment I'm not sure if I like to go that way. Which approach is better from a performance point of view? One "big" table or more smaller ones connected by an identifier?

For my problem a solution is to define a variable with the 'text'-function like:

varTab = '[' & text(GetFieldSelection(tabMatFam)) & '-' & text(GetFieldSelection(geometry_2)) & '_field1]'

Now I can call this variable in my charts with avg($(varTab)) and get a comparable behavior like the collection box, which I used before, but thanks to the parameter without the need to define a display condition or something else.

I think an implementation with one (better) table would be smarter, but … 😉

[marcus_sommer · Accepted solution]

In the most scenarios a big fact-table with all measure-fields + n dimension-tables with additionally information will perform better as any other kind of data-model. Particularly the key-fields which are needed to associate multiple fact-tables could decrease the performance significantly and especially with link-table approaches could the artificial in between table become much bigger as the origin data.

Beside of this all essential logic belonged into the data-model and not within the UI. A lot of things are much easier to solve within the data-model as in the UI, could be externally controlled and most of it is re-usable between various data-models. Re-using more or less hard-coded UI logic is much more expensive.

Therefore the big question is why making very simple things much more complicated? So the simplest approach is ALWAYS to start with a star-scheme - just concatenating the fact-tables and associating a few dimensions. Not everything needs to be completely harmonized/cleaned/prepared at the beginning else it could be done step by step. Loading it into an empty application and then using a few table-charts with simple sum() and count() & few list-boxes and the data and their quality as well as their associations could be validated.

In most cases you got a working prototype in a single workday. If there are really more complex requirements you may need one or two more workdays to implement them or to detect the challenges and efforts which they may cause. Quite often you could remain by the star-scheme and without special needs such model mustn't be mandatory optimized from a performance perspective. 

In regard to your variable-approach the text() isn't necessary. Also the getfieldselections() isn't needed for your aim because the logic will only work for a single selection in those fields. So it might be simplified with something like this:

varTab = tabMatFam & '-' & geometry_2 & '_field1'

and the call may look like:

avg([$(varTab)])