topic Avoid $(-expansion in SetAnalysis Variable in QlikView

Avoid $(-expansion in SetAnalysis Variable

hugmarcel — Tue, 21 Apr 2015 14:34:15 GMT

I'd like to set a formula in a script variable vBudgetAmount:

SET vBudgetAmount = SUM({<$(='['&CONCAT({1<$Field={'Service Request - *'}>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

The formula calculates the sum of Budgets, ignoring all the selections in all table fields starting with 'Service Request - '

Goal is to create variable vBudgetAmount having content:
SUM({<$(='['&CONCAT({1<$Field={'Service Request - *'}>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

But I always find that the $( is causing problems, thus I am getting:
SUM({<>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

instead.

How I can get this working?

Thx - Marcel

Re: Avoid $(-expansion in SetAnalysis Variable

Peter_Cammaert — Tue, 21 Apr 2015 14:42:57 GMT

Something like this?

LET vBudgetAmount = 'SUM({<$' & '(=' & chr(39) & '[' & chr(39) & '&CONCAT({1<$Field={"Service Request - *"}>} DISTINCT $Field,' & chr(39) & ']=,[' & chr(39) & ' )&' & chr(39) & ']=' & chr(39) & ')>} Budgets)';

I may have missed something, and it definitley looks horrible, but you get the idea...

Best,

Peter

Re: Avoid $(-expansion in SetAnalysis Variable

stigchel — Tue, 21 Apr 2015 14:56:34 GMT

There seems nothing wrong with your expression, can you try to create the variable in the front and look at the result?

Otherwise there may be something wrong with $Field={'Service Request - *'}>} not matching any fields (do you need the space at the end?)

Tested your expression in the following qvw

Re: Avoid $(-expansion in SetAnalysis Variable

Peter_Cammaert — Tue, 21 Apr 2015 15:04:03 GMT

Precisely, in the front-end this works out ok because this is a later stage.

The OP asked how to accomplish this in the script, where $-sign substitution will wreak havoc everywhere. Even in a SET statement.

Pobably Marcel wants to define complex expressions in a single spot, and use them in multiple expression fields in order to avoid having to copy-paste every change everywhere.

Re: Avoid $(-expansion in SetAnalysis Variable

stigchel — Tue, 21 Apr 2015 15:41:46 GMT

You're right Peter, didn't realize that

Re: Avoid $(-expansion in SetAnalysis Variable

hugmarcel — Tue, 21 Apr 2015 19:28:07 GMT

Exactly, you got the point. It must be done in script!

Marcel

Re: Avoid $(-expansion in SetAnalysis Variable

hugmarcel — Thu, 23 Apr 2015 10:41:01 GMT

The (simple) solution is to create a variable in script and use the variable in the formula. E.g. (dummy code):

let vServiceRequestFields = '';

for all fields in table 'Service Request'
let vServiceRequestFields = vServiceRequestFields & '[' & field & ']=,';
next

SET vBudgetAmount = SUM({<$(vServiceRequestFields)>} DISTINCT Budgets)

Marcel