Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

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

cancel
Showing results for 
Search instead for 
Did you mean: 
d_pranskus
Partner - Creator III
Partner - Creator III

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

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.

Labels (1)
Comments
jfkinspari
Partner - Specialist
Partner - Specialist

It's crazy solution - but works

0 Likes
carbal1952
Creator II
Creator II

Regarding QlikView, Nothing is too crazy (I think).

I like this kind of ideas.

CB.

0 Likes
Carlos_Reyes
Partner - Specialist
Partner - Specialist

Awesome... I would never have come up with that kind of solution...

0 Likes
rustyfishbones
Master II
Master II

Excellent Darius,

Thanks for sharing! now if you could just share the framework you built it would be much better

0 Likes
flipside
Specialist II
Specialist II

Nice solution, but could you not just add extra parameters in, such as ...

 

SUM({$< $1, $2, $3, $4 >} quantity)

... up to as many as you think you'll need. No requirement to parse the field list then.

flipside


0 Likes
qlikviewwizard
Master II
Master II

Hi Darius Pranskus

Thanks for sharing. Could you give me an example/attach sample qvw file for better understanding. Thanks in advance.

alexdataiq
Partner - Creator III
Partner - Creator III

Yeah, a convoluted workaround but It worked for me too.

Thanks

0 Likes
marcus_sommer
MVP & Luminary
MVP & Luminary

If the comma will be replaced by it's ascii code - chr(44) - it will be work without further adjustments, see also: Re: nested variable troubles

- Marcus

0 Likes
alexdataiq
Partner - Creator III
Partner - Creator III

I actually tried using Chr(44) before Replace, but couldn't make it work when sending several parameters to an expression, when there are multiple values per parameter and those values might include white spaces.

This is a simplified example:

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

Didnt work in my expression:

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

Any thoughts?

Cheers

0 Likes
marcus_sommer
MVP & Luminary
MVP & Luminary

What happens if you used as expression (variablen-call)?

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

- Marcus

0 Likes
Version history
Last update:
‎2012-04-23 01:26 PM
Updated by: