2 Replies Latest reply: Jun 22, 2011 6:05 AM by Thomas Staffelbach RSS

    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

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

          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.