topic Re: Loading calculations from excel or load script in QlikView

Loading calculations from excel or load script

ericdelaqua — Sat, 16 Nov 2024 04:23:43 GMT

Hello,
I am hoping there is a way to do this. I am trying to load calculation from excel or even the load script
but when I add the field to the measure column it just displays the text and doesn't evaluate.
My sample code is below + the output am getting.Thanks

transactions:
Load * Inline [Code,Value,Position,Date
b230s,500,1,18/02/2019
bd43p,700,2,18/02/2019
bv891,350,3,18/02/2019
b230s,1000,1,20/02/2019
bd43p,1400,2,20/02/2019
bv891,700,3,20/02/2019
];

Dims:
Load *,Code as Key;
Load * Inline [Code,Desc
b230s,Chair
bd43p,Table
bv891,Fan
bb232,TV
BN656,Home
];

Calculation:
Load * Inline [Key,Calc
b230s ,Sum(Value)
bd43p ,Sum(Value)
bv891 ,Sum(Value)
bb232 ,Sum({<Key={'bv891'}>}Value)+Sum({<Key={'bd43p'}>}Value)
BN656 ,Sum({<Key={'b230s'}>}Value)-Sum({<Key={'bd43p'}>}Value)
];

Re: Loading calculations from excel or load script

saifuddin — Wed, 20 Feb 2019 03:00:57 GMT

Cause Qlik reading those calculation as field. For your data, you don't have to do any calculation in load script. Just load the data from excel and then do the calculation in chart.

Thanks

Re: Loading calculations from excel or load script

Shubham_Deshmukh — Wed, 20 Feb 2019 06:58:01 GMT

WHy you are not doing that calculation in expression?

Re: Loading calculations from excel or load script

shiveshsingh — Wed, 20 Feb 2019 07:11:41 GMT

It will not be evaluated in script as it is behaving like a column. Moreover you need to write set expressions in chart expression.

Re: Loading calculations from excel or load script

marcus_sommer — Wed, 20 Feb 2019 08:01:42 GMT

Your approach won't work because Qlik doesn't evaluate the Calc field as expressions else they are just strings. The only way to get such an expression-string to be evaluated is to wrap it with a $-sign expansion like: $(= Calc) but this creates an adhoc-variable and a variable could have just one content at the same moment and this means the first finding of Calc will be evaluated and applied for all rows.

The only way to apply different expressions for the various rows in an object is querying each dimension-value and return the wished calculation. In simpler scenarios this might be a nested if-loop and by more complex situations a pick(match()) would be more appropriate, like:

pick(match(Dim, 'val1', 'val2', ...), expr1, expr2, ...)

whereby both the dimension- as well as the expression-list might be the content of a table and called with a $-sign expansion like:

$(=concat(Dim, ','))

The main-logic here is simple but depending on the content the return-list might need some extra quoting like:

'$(=concat(Dim, chr(39) & ',' & chr(39)))'

- Marcus