topic Re: Expression behaving differently when loaded as a variable in QlikView

Expression behaving differently when loaded as a variable

Thu, 26 Jun 2014 14:45:47 GMT

I have the following expression, which I was using in a few places:

sum({<YearMonthField={'<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)'}>}Plays)

I started moving some common expressions into a CSV file and importing them in script as variables, so that I could keep everything in one place. When I did this, the above expression started giving a different result.

It's being imported in my file as:

"var4a","sum({<YearMonthField={'<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)'}>}Plays)"

To debug, I created a straight table where I put 2 columns side by side - one is the literal expression, the other is just $(var4a).

I attached a picture of what I'm seeing. It's like the "=max(YearMonthField)" parts of the expression are not being evaluated when it's loaded as a variable.

Any ideas? Thanks!

Re: Expression behaving differently when loaded as a variable

sudeepkm — Thu, 26 Jun 2014 14:50:21 GMT

EDIT:

sum({<YearMonthField={"<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)"}>}Plays)

pls use "$(=max(YearMonthField))" inside set analysis

Re: Expression behaving differently when loaded as a variable

Thu, 26 Jun 2014 14:57:19 GMT

So it's just double quote instead of single quote?

How would I load this in my CSV file of expressions? I tried a \" to escape the quote in the expression, but it seems to literally load \" instead of just ".

Re: Expression behaving differently when loaded as a variable

Thu, 26 Jun 2014 17:24:51 GMT

I changed my input file from CSV to XLS so that I could more easily embed the double quotes in the expression values. I reloaded everything, and I am still seeing the same issue, so this does not seem to have helped.

Re: Expression behaving differently when loaded as a variable

Fri, 04 Jul 2014 15:53:24 GMT

hey I'm not sure if this will resolve your issue but I had the same end result where a piece of the expression within a dollar expansion wasn't being evaluated when passed using a variable and found a solution so am just passing it on in case of use.

Background of my issue - to improve transparency and maintenance, I defined my expressions in the script with variables, which I then used in all my charts and tables etc.

When I had my expressions defined directly in the chart/table, they worked grand, but when I moved those exact same definitions into variables some didn't work because a dollar expansion wasn't getting evaluated correctly.

For example I had the following set analysis expression:

sum({1<

TDate={'$(vSelTDate)'}

,IsTDay={'Y'}

,Dept={'HR'}

>}[Total Qty])

I converted this to a variable as follows (note that I used double quotes around the set analysis values and the whole variable definition is between 2 single quotes):

LET vTotalQtySelDate =

'sum({1<

TDate={"$(vSelTDate)"}

,IsTDay={"Y"}

,Dept={'HR'}

>}[Total Qty])';

However the TDate filter in this would not evaluate correctly - like your example, it came out as

TDate={""}

So after a search on this forum, I found a fix/workaround that has resolved this for me; when the expression of your variable contains a dollar expansion, you have to separate out the $ symbol into its own string.

So with the above example, the following definition works perfectly.

As this can be hard to read in a browser, I'll just point out that TDate={ is followed by a double quote which is the start of the value to be passed in to filter the TDate field and then a single quote to end the string.

LET vTotalQtySelDate =

'sum({1<

TDate={"' & '$' & '(vSelTDate)"}

,IsTDay={"Y"}

,Dept={'HR'}

>}[Total Qty])';

hope it helps anyways!

Re: Expression behaving differently when loaded as a variable

jaimeaguilar — Fri, 04 Jul 2014 18:40:50 GMT

Hi,

you can also use functions Chr(34) for double quotes or chr(39) for single quotes as escape functions,

regards