topic Re: Evaluating expressions saved in a table in App Development

Evaluating expressions saved in a table

ali_hijazi — Tue, 21 Mar 2023 15:53:17 GMT

Hello
got 2 tables T1, and T2
T1 has two fields called NAME and Expression
under NAME there are names of fields in another table:
NAME Expression
-------------------------
sales sum(sales)
returns sum({<product={P1,P2}>}returns)

in T2 there are 2 fields named sales, and returns along with other fields
what I want to accomplish in a table is the following:
add dimension NAME, Expression, and Measure
so the output would be:
SALES, SUM(SALES), 123456
RETURNS, sum({<product={P1,P2}>}returns), -456732

Kindly advise on how to accomplish this

Re: Evaluating expressions saved in a table

Lisa_P — Tue, 21 Mar 2023 23:15:28 GMT

You can calculate this in a KPI like this:

returns
=$(=Only({<NAME={returns}>}Expression))

Re: Evaluating expressions saved in a table

ali_hijazi — Wed, 22 Mar 2023 12:35:24 GMT

Hello @Lisa_P
thank you for your reply
but this only evaluates one expression
what I want is to evalute each expression as I put them in a table
so each row should give me a number next to each NAME

Re: Evaluating expressions saved in a table

marcus_sommer — Wed, 22 Mar 2023 13:23:42 GMT

Such approach isn't possible because your expression remains a string and won't be evaluated (unless with the above shown $-sign expansion which creates an adhoc-variable for just a single value/expression).

The only way to apply different expressions for each row is the use of a conditional logic like:

pick(match(Name, 'x', 'y', 'z'), sum(Field), avg(Field),max(Field))

Re: Evaluating expressions saved in a table

ali_hijazi — Wed, 22 Mar 2023 15:11:31 GMT

this approach only evaluates on expression
Field Expression value
Field1 max(Field1) 800
Field2 sum(Field2) 1500
...

I have Field and Expression in the schema
next to each Field and Expression I want to compute / evaluate the Expression and display the value

Re: Evaluating expressions saved in a table

marcus_sommer — Wed, 22 Mar 2023 15:46:01 GMT

it's just an example to show the needed logic for such an object. Most users which want to apply such an approach just use it like above by writing the dimension-value to expression matching manually. It's quite simply and will work well - unless by larger data-sets because it's the opposite of an optimized way to calculate data.

Nevertheless this doesn't mean that the dimensions and expressions couldn't be maintained within a table - but like mentioned you couldn't use it directly else you would need to aggregate the dimensions and also the expressions per concat() within single strings which could be afterwards evaluated with the above shown $-sign expansion. It's not trivial because you need to synchronize both listings - maybe with a rowno() in the load as sorting-parameter - and probably more tricky to consider the various kinds of quotes and brackets within the multiple list-values and by aggregating them and calling the resulting adhoc-variables.

IMO it's a rather bad way to design UI views and I couldn't recommend it - even if it's technically possible.

Re: Evaluating expressions saved in a table

Lisa_P — Thu, 23 Mar 2023 01:02:07 GMT

Maybe this is an approach that you can use ...

https://community.qlik.com/t5/Design/Qlik-Sense-Custom-Report/ba-p/1471797