Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
derekjones
Creator III
Creator III

Recalling Expression from a table in Qlik datamodel using a Dimension

Hello all

I need your help please. I am using an 'Expressions Table' in my Qlikview datamodel to store all my expressions. I have done this many times before with no issues, however how I recall the expression usually is by full set analysis to bring back the single Expression or by a listbox to reduce the possible records to one. 

I however now want to take this one stage further and try and recall an expression using one of its table dimensions as an actual dimension in the graph/table. 

I have attached a simple example, but essentially I have an Expression table with two expressions (Revenue and Cost) and then a simple datatable with Revenues and Costs as columns:

derekjones_1-1616064391055.png

 

So, I can recall the ExpressionValue in a straight table as text by using =only(ExpressionValue)..

derekjones_2-1616064444050.png

I can also use that ExpressionValue specifically using set analysis to restrict ExprMeasure e,g for Cost it would be =$(=only({<ExprMeasure={'Cost'}>} ExpressionValue)) ..

However, if I then want to just use =$(=only(ExpressionValue)) and have ExprMeasure as a dimension in my straight table it does not work?!?

I don't want to use the above workaround of one expression per ExprMeasure as then I'm maintaining separate Measures in my dashboard which in my real work example I have over 20 measures

Is this just not possible to do, seems it should be possible and would save me a ton of maintenance.

Example app attached

Thanks in advance for any help

 

 

 

 

 

 

 

 

1 Solution

Accepted Solutions
Denisg
Contributor III
Contributor III

Hi,
It depends if your new fields in the Expressions table are dimensions or expressions.

If they are dimensions as we have in your last example, then you can keep the same principle with only one expression per records, and so only one PickListValue. See my attached version script with the IDs 1,2,3,4.

Now, if you consider the ExprTime will be a an expression to evaluate in the chart (like "=MonthStart(Today(),-1)"), then it is more complex. But in that case I would recommend to go back to above solution: keep that field as a dimension (MTD, LM), and to regroup all the expressions of the record into a single expression field ExpressionValue. Then, you would have only one vPickListValue to manage

Cheers

Denis

View solution in original post

8 Replies
Denisg
Contributor III
Contributor III

Hi,

I don't think you can do it with only(ExpressionValue).  

However I have a workaround in picking a value in a list.
It is dynamic as you wanted. For that I did the below changes in your app:

Script:
- Added an ID field to the Expression table
- Created a vPickListValue string containing the list of ExpressionValue:

Concat: LOAD Concat(ExpressionValue, ',', ID) as ValueConcat RESIDENT Expressions;
LET vPickListValue = peek('ValueConcat');
DROP Table Concat;

Chart:
- Dimension: Adding ID
- Expression: Using a Pick function based on the ID of each line:

=Pick(Only(ID), $(vPickListValue))

See in the attached application, it works.

Best luck.

Denis

 

marcus_sommer

What do you trying to do isn't possible because you couldn't evaluate the field-content as an expression unless using a $-sign expansion but this creates an ad-hoc variable with a single global value and couldn't be used within a dimensionally context.

This means you need to use a workaround like provided from Denisg  or you may adjusting your datamodel by transforming your crosstable-measures into stream-data measures - means not:

load ID, Cost, Revenue ... from ...;

else something like this:

load ID, KPI, Value ... from ...;

and KPI = 'Cost', 'Revenue', ... and in Value are your numbers. With it you could simply use KPI as dimension and sum(Value) as expression.

- Marcus

derekjones
Creator III
Creator III
Author

Thanks Denis

Apologies in delay of responding, I was offline last few days. In my research I did come across this solution however in my real life problem it doesn't quite solve it as far as I can tell.

The example I included in this post is actually too simplified. It is only measuring:

  1. A Measure
  2. An Expression

In my real life example, the expression table contains many more fields, but for simplicity let us just add one more here to highlight how Pick would be challenged in solving this...

So aswell as having a measure like 'Cost', I also have a column to determine 'Time' e.g. is this expression a sum of cost for 'This Month', 'Last Month', 'Year to Date' etc. In my example I've just amended to hard code the dates I have in my data to each Time dimension, but it should give you a view of what I'm doing..

derekjones_0-1616577864694.png

 

All I can think of is maintain a separate pick variable for each time variant, but that then means if I add another dimension to that expression table (example in mine its the user role) it all multiples out again.

Updated Qlikview model attached if it helps.

 

Thanks again for your help

 

 

derekjones
Creator III
Creator III
Author

Thanks Marcus

Apologies for not responding sooner, I've been offline the last few days.

Yeah, it is the $ sign expansion creating a single global value. Such a shame as if it worked that would be a powerful option as you effectively control the expression in each individual cell of a chart. I have many dashboards where I individually have to create separate tables for different metrics as cannot combine them as individual rows. This makes it a pain when you want to resize columns etc as you have to individually resize each straight table. Same with export to excel, as not a single table, cannot do.

I like your idea on changing to stream data, again my example was too simple to provide you all my nuances. I have metrics I need to include which are not simple sums e.g. distinct counts, rangesums etc so again I'm stumped on these unless I fundamentally change the datamodel behind to try and cater for these too which on some I think could be achieved, others I think I would get stuck on.

Thanks again,

Derek

 

 

marcus_sommer

I assume it's completely intentionally from Qlik that there is no evaluate() as function available within the UI - in opposite to the script in which you could use it. Technically it shouldn't be very difficult to implement such a feature whereby I wouldn't be surprised if they had it developed/included within the first releases and removed or deactivated it afterwards.

Background for this decision is probably that Qlik wanted to prevent exact such use-cases like yours - meaning Qlik didn't want to work it like a table-calculation in which you could easily create a table-view with different expressions for each single cell relating to numerous cells respectively cell-areas inside and outside from this table - quite often in mix of data-source, evaluation and layout.

BI tools and databases are working different from this approach and Qlik is here no exception. But this doesn't mean that you couldn't create there appropriate views else usually you have much more features and possibilities as with the table-calculation. Essentially for it is to build a suitable datamodel and UI for the given requirements.

I'm quite sure that's not the feedback what do you want to see here and I could comprehend your approach very well but in general it's the wrong direction to apply n different calculations per row / column within an UI object. Sometimes I would have liked an exception but if Qlik enables this feature mass of people would abuse it and wondering why it's not working like expected, instable and quite likely very slow even with rather small datasets.

- Marcus

derekjones
Creator III
Creator III
Author

I sort of agree, I can see how this could get abused, but no more than complex aggregations could make a calculation monster. In fact in my model, every individual cell has it's own expression I have to build and load manually so it's quite labor intensive to setup yet front end it would allow all results to be in one single table.

But as you point out, not hard to implement so must be intentionally excluded. I wonder if now on the new Qix engine if that makes a difference and could be reconsidered, similar to how sortable aggr function was introduced in v12 via a structured parameter etc

Anyway, for now, I'm back to separate charts for each measure, at least Qlik allows that so I can get the result I need albeit with more upfront maintenance.

Thanks again for at least taking time to have a look for me and confirming my suspicions

 

 

Denisg
Contributor III
Contributor III

Hi,
It depends if your new fields in the Expressions table are dimensions or expressions.

If they are dimensions as we have in your last example, then you can keep the same principle with only one expression per records, and so only one PickListValue. See my attached version script with the IDs 1,2,3,4.

Now, if you consider the ExprTime will be a an expression to evaluate in the chart (like "=MonthStart(Today(),-1)"), then it is more complex. But in that case I would recommend to go back to above solution: keep that field as a dimension (MTD, LM), and to regroup all the expressions of the record into a single expression field ExpressionValue. Then, you would have only one vPickListValue to manage

Cheers

Denis

derekjones
Creator III
Creator III
Author

Sorry for delay Denis, I was on a break from work for Easter so only caught up with emails.

Thanks, this solution works, now to put into practice 😣