topic Re: Variable Arguments containing comma in QlikView

Variable Arguments containing comma

abe786 — Tue, 17 Nov 2015 15:44:38 GMT

We are trying to centralize all the calculations used in dashboard via variables containing expressions. These variable definition will be stored in external file. However, we have run into problem that if a variable argument contains comma, qlikview treats it as next argument. I've researched a bit and found people have given a workaround, to use a different character like ; instead of , and use replace in variable definition to replace semi colon with comma.

Comma problem (,) workaround for dollar sign expansion with parameters

BI Review: How to write reusable and expandable expressions in QlikView

However, this solution is not working for me. When i try to implement it, if I use SET in variable definition, the $ expansion of replace gives out null. If I use LET, then I get script error. If i use LET and put expression in single quotes, similar behavior as SET happens. The only way, I could implement it is if I put the variable definition in front end, however I can't go that way, since we are moving definition to external file.

Ideally this solution should have worked as per above links. Can anybody look at my sample code and tell me what I'm doing wrong.

Help Appreciated

Re: Variable Arguments containing comma

rwunderlich — Tue, 17 Nov 2015 16:22:15 GMT

Dealing with literal $ and quotes in LET/SET can be a pain. I use proxy characters (@ for $, | for ') and replace them with MapSubstring. It's flexible and handles any number of special characters.

EscapeCharsMap:

MAPPING LOAD * INLINE [

from, to

@,$

|,'

];

LET vExpr = MapSubString('EscapeCharsMap',

'SUM({@< @(=Replace($1, |;|, |,|)) >} quantity)'

);

BTW, you shouldn't quote $1 if you are passing it quoted.

You can avoid this issue altogether by storing the variables in an external file and using peek() to populate the variable. No interpretation will be done by peek. See

Storing variables outside of QlikView - The Qlik Fix! The Qlik Fix!

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Variable Arguments containing comma

abe786 — Thu, 19 Nov 2015 15:09:05 GMT

Thanks so much @Rob for taking the time to reply

However, the mapsubstring method is not working for me. See attached qvw.

I was able to work it via peek method from the link you had shared. However, there is a issue if the passed variable contains a single quote, then it doesn't work. For e.g. for

vExpr defined as SUM({<$(=Replace($1, ';', ','))>} quantity) in Excel sheet, this works

=$(vExpr('year={2014};month={11}')) in expression

However, below doesn't work:

=$(vExpr('year={'2014'};month={'11'}'))

In my project, i have to pass non-numeric set conditions, so unable to work it out

Any suggestions?

Re: Variable Arguments containing comma

rwunderlich — Thu, 19 Nov 2015 18:10:57 GMT

The MapSubstring method works fine in the attached example. I think you just had to many test variables going and were referencing the wrong one in the chart.

For the quote issue, use the escape syntax for single-quotes, which is to type the single-quote twice.

=$(vExpr(year={''2014''};month={''11''}))

This is shown in the example as well.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com !