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

[d_pranskus]

Hello

One reason why you want to use dollar sign expansion is the possibility to store your expressions in variables and then use these variables in your chart expressions. THis will concentrate all business logic in one place and simplify chart expressions. But suppose you want to pass some parameters into these expressions. QlikView supports this, but there is very annoying problem. If there are any commas (,) in your parameter value, QlikView treats this as parameter separator but not as part of parameter value.

Recently I found some workaround for that. Suppose we have and expression stored in a variable varExpr. The value o expression is SUM({$< $1 >} quantity).

Now if I create a chart expression =$(varExpr(year)), this will convert into =SUM({$< year>} quantity) and it will calculate sum ofd quantity field with year field cleared. But what if I want to clear not just year field but month as well. I cannot do this by setting my chart expression to =$(varExpr(year, month)) this will not work and as a result I get the same expression =SUM({$< year>} quantity).

But If I use SUM({$< $(=REPLACE('$1', ';', ',')) >} quantity) for variable value and then =$(varExpr(year; month)) for chart expression - this will give me the expression I want =SUM({$<year, month>} quantity).

Cheers

Darius.

[alexdataiq]

I didn't quite get your question. If I try with single quotes as your example it doesn't work either. It gives me an error: Error in set modifier ad hoc element list: ',' or ')' expected.

Cheers

[marcus_sommer]

Yes, the single-quotes must be doubled:

$(vExpression(''CENTRAL NORTH'', ''Company A'' & Chr(44) & ''Company B''))

- Marcus

[alexdataiq]

Unfortunately using double single-quotes didn't work either. Actually the following expression which is simpler doesn't work:

$(vExpression(''CENTRAL NORTH'',*)

I believe this is caused because the blank spaces. After trying several combinations of quotes, double quotes and Chr()  I ended up using Replace() , has worked good so far.

Cheers

[marcus_sommer]

I have now taken a closer look and you are right - it didn't worked in this way. The reason is that the parameter-variables will be considered with the context in which they are used. In my example-link from above the replacing from the comma per ascii-code of chr(44) worked because it was evaluated within the variable.

In your example instead the parameter is only a string without any evaluation. But with a slight adjustment it could be changed. For me worked the following approach:

$(vExpression('CENTRAL NORTH', '"Company A"' & Chr(44) & '"Company B"'))

vExpression = Sum({<Region={$1}, Company={$(=$2)}>} Amount)

with single- and double-quotes and a $-sign expansion around the variable-parameter.

- Marcus

[andy]

I had my comma-separated list in another variable and wanted to put that as a parameter into the expression. Then it looks like this

In script:

Set vMyList=year, month;

Usage in object:

$(=replace('$(varExpr(~(vMyList)))','~','$'))

[#7]

Excelent!

[Qlik1_User1]

@d_pranskus ,

I am using replace as well but its not working for me, please suggest what am I missing here

SET vMoneyconversion2 =
DUAL(pick(match(-1,$(=Replace('$1',';',','))>=1000000000,$(=Replace('$1',';',','))>=1000000,$(=Replace('$1',';',','))>=1000,$(=Replace('$1',';',','))<1000),
num(round($(=Replace('$1',';',','))/1000000000,0.1),'#,##0.0 B'),
num(round($(=Replace('$1',';',','))/1000000,0.1) ,'#,##0.0 M'),
num(round($(=Replace('$1',';',','))/1000,0.1),'#,##0.0 K'),
round($(=Replace('$1',';',',')),0.1)),$(=Replace('$1',';',',')));

$(vMoneyconversion2(Sum({<id={'active'} ;is_imp={'1'};type_name-={'N/A'}>} (sales*Rate))))