Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Problem with assigning result of an expression to a variable

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

Tags (1)
1 Solution

Accepted Solutions
gussfish
Contributor II

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

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.

2 Replies
gussfish
Contributor II

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

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.

Not applicable

Problem with assigning result of an expression to a variable

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

Community Browser