topic Problem with assigning result of an expression to a variable in QlikView

Problem with assigning result of an expression to a variable

Tue, 21 Jun 2011 05:49:44 GMT

The following expression works fine when I use it in a QlikView control:

=Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'$(=Date(Min(fact_ReconciliationTransactions.transaction_date) - 1))'}>} fact_ReconciliationStockValueSAP.stock_value)

but it does not work when I try to use it a script e.g. like this

LET vTest = Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'$(=Date(Min(fact_ReconciliationTransactions.transaction_date) - 1))'}>} fact_ReconciliationStockValueSAP.stock_value)

I have also tried the $-expansion but did not manage to get the expression to function. I always get the following error:

Aggregation expressions not allowed in GROUP BY clause

LET vTest = Sum({$<fact_ReconciliationStockValueSAP.sap_stock_value_date = {'(internal error)'}>} fact_ReconciliationStockValueSAP.stock_value)

What am I doing wrong?

Thanks,

Thomas

Re: Problem with assigning result of an expression to a variable

gussfish — Tue, 21 Jun 2011 06:06:49 GMT

At issue is that the scripting language is deceptively similar to the UI expressions language - but not the same! The two differences that are tripping you up are

1. set expressions are not supported in scripts

2. aggregation functions are only for evaluation in the context of LOAD or SELECT statements.

So, if you really want to get an aggregated value into a variable, you'd need to construct a LOAD statement that calculates the aggregation you want and has a WHERE clause to do the filtering that's equivalent to your set expression, and that results in a single record containing that value

(i.e.

MyAggr:

LOAD SUM(fact_ReconciliationStockValueSAP.stock_value)

WHERE ;

) ;

and then use peek() to get the value into your variable.

Problem with assigning result of an expression to a variable

Wed, 22 Jun 2011 10:05:20 GMT

Thank you so much! It never occured to me that I'm trying something that cannot work...