Variables in .txt file brought in script via include statement

[naziralala]

Hello All,

The following script is in an include statement:

Set eRevenue = if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-1 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-2 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))));

Can you advise what is the issue in this as it is not working?

Also, in same file I have script as below which is working in chart:

Set eRankRevenue = rank(eRevenue);

The same script is working in variables in charts.

Thanks,

Nazira

[Oleg_Troyansky]

Hi,

I think your expression doesn't work because of the nested single quotes that cannot be easily resolved. Generally speaking, you can either use the function REPLACE() to replace single quotes by another symbol and then replace the other symbol back to single quotes.

In your particular case, however, it seems like you should simply remove the first single quote, right after the equals sign, and the rest should be OK. I'm not looking into the validity of the expression logic and into the performance aspects of it (your expression can be simplified and improved substantially).

In broader terms, you could avoid some of these problems if you stored your expressions externally in a text file and loaded them into your load script, generating the corresponding variables in the process of the load. That helps avoiding issues caused by nested quotes, $-sign expansions etc...

I'm describing this technique, along with many other advanced techniques, in my book QlikView Your Business - check it out.

cheers,

Oleg Troyansky

[naziralala]

Hey Oleg,

I am using an excel file. When I take the single quotes off, it gives me an error.

Please advise.

Thanks

[swuehl]

Could you post your excel file containing the variable definition and the script you are using to read the variables in?

[Oleg_Troyansky]

For starters, replace your Excel file with a text file - CSV, for example. Excel files are inconvenient for two reasons:

First, these are not text files, so you can't easily compare two files and find differences using an advanced text editor.

Second, they apply their own quoting and distort your formulas - exactly what's happening to you.

You won't have this problem with a regular text file, not managed in Excel.