- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
It's crazy solution - but works
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Regarding QlikView, Nothing is too crazy (I think).
I like this kind of ideas.
CB.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Awesome... I would never have come up with that kind of solution...
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Excellent Darius,
Thanks for sharing! now if you could just share the framework you built it would be much better
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi Darius Pranskus
Thanks for sharing. Could you give me an example/attach sample qvw file for better understanding. Thanks in advance.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Yeah, a convoluted workaround but It worked for me too.
Thanks
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
What happens if you used as expression (variablen-call)?
$(vExpression('CENTRAL NORTH', 'Company A' & Chr(44) & 'Company B'))
- Marcus