Using Variables for Expression loaded into a Table in Load script

[jonathan_dau]

Good morning all, I've been working for quite some time with Qlik but I'm stuck with something I wanted to try in Qlik Sense. 

The idea is basically to build a Scorecard in a straight table using a definition loaded from an Excel table. All metrics and expressions would be defined in the Excel file and loaded into the script. 

I do that in 2 steps. 

1. I load and create variables for expressions in the load script. My variables are created properly in the front end. 

Variables:
LOAD
ID as _ID,
Object as _Object,
[Metric Group] as _Metric_Group,
MetricName as _MetricName,
Description as _Description,
MetricCalculation as _MetricCalculation,
Format
FROM [lib://Folder_QVD/CEO Cockpit\Data\CEO_Cockpit_Variables.xlsx]
(ooxml, embedded labels, table is Variables);

for i = 0 to NoOfRows('Variables') - 1
let vName = peek('_MetricName',i,'Variables');
let vComment = peek('_Description',i,'Variables');
Let $(vName) = peek('_MetricCalculation',i,'Variables');

next i

2. I create the Dimensions and Measures I will be using in the straight table in an excel file and load into the script as data island table

MeasuresName	ScorecardID	ScopeDefinitionParameter	ActualMeasures
Ticket Net Revenue	1	Budget	vNetRevenue
Ticket NPD	2	Budget	vNetPerDiem
OBR Gross Revenue	3	Budget	vNetRevenueOnboard
OBR PPD	4	Budget	vNetPerDiemOnboard
Issue Count	5	Volume	vIssues

3. I want to use this in my straight table. Dimensions are working fine but issues start with Expressions

I have been trying to resolve the Expressions that should be using Variables already created and I've made something like that Num($(=chr(36)&chr(40)&Only(ActualMeasures)&chr(41)),ActualFormat). Here I'm adding a formatting as well. 

When I select 1 Measure it works fine, but when I don't select any Measure I have the other output below

I'm using Only to make sure that each variable is mobilized for only the right Measure. My measures and variables are unique so I'm loosing it here. 

Being trying different things with Only, Match, Pick etc, etc... without any success. 

Would you guys have methods for that? 

Would you also recommend another approach? I want basically to avoid nested Ifs, I also want to avoid Pick(Match(Concat complex things so wanted to use this simple approach. 

Hope you have something for me!!

Thanks

Jon 

[jonathan_dau · Accepted solution]

Actually, this was answers in another post that I answered too a while ago. 

https://community.qlik.com/t5/QlikView-Creating-Analytics/Expressions-in-Fields/m-p/393906/thread-id...

Thanks jontydkpi

The problem is that $ expansions are performed before the chart data is processed and outside the context of the dimensions of the chart, so something like $(=Only(Expression)) does not work as no unique value of Expression exists at the time of expansion. If you select one row in the table, the expression evaluates correctly.

To get around this, you need to assemble your expressions into a Pick(ID, expr, expr, expr, ....) statement. You can do this with this script fragment:

   T_ConcatExpressions:

     LOAD Concat(Expression, ',', ID) As ConcatExpression

     Resident Expressions;

     Let vMetrics = 'Pick(ID,' & Peek('ConcatExpression') & ')';

     DROP Table T_ConcatExpressions;

Now you can create a straight table at the front end like this:

     Dimension:  ID

     Column 1:    Name

     Column 2:    $(vMetrics)

You can hide the dimension column, or even use Name as the dimension as long as each expression has a unique name.

Note the Concat statement needs the ID to ensure that the concat sorts the expressions by ID.

Do not be conerned about the size of the vMetrics expression. I have done this with concats that produce hundreds of lines in vMetrics. However, depending on the size of the data set and the number of expressions, evaluating this statement can becomes processor intensive. I get good performance with 50+ expressions on a 300,000 rows data set using a dual core PC.