topic Re: Variables in .txt file brought in script via include statement in QlikView

Variables in .txt file brought in script via include statement

naziralala — Fri, 20 May 2016 19:30:50 GMT

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

Re: Variables in .txt file brought in script via include statement

sunny_talwar — Fri, 20 May 2016 19:35:31 GMT

Script is running into an error or the expression is not working on the front end?

Re: Variables in .txt file brought in script via include statement

trdandamudi — Fri, 20 May 2016 19:40:59 GMT

Need more information as Sunny mentioned... it will help us to identify the issue, Also try eRevenue expressions individually and see if you are getting the expected results and that will give you a clear idea where it is going wrong if any.

Re: Variables in .txt file brought in script via include statement

naziralala — Fri, 20 May 2016 19:43:38 GMT

The same script is working in the chart...tried individually..

Re: Variables in .txt file brought in script via include statement

ellenblackwell — Fri, 20 May 2016 21:39:54 GMT

Not sure if this is the same problem, but I have experienced similar behavior when pulling expressions in from Excel. The problem with Excel is related to hard returns in the individual cells which contain the expression. Only the first line of the expression was coming in and being evaluated.

For example, the following expression is written for easy reading/understanding, and it works fine in a QV object:

=If([Product]='A', sum(Sales),
if([Product]='B', sum(Sales)-sum(Taxes))
)

However, for the full expression to come into QV from an Excel cell, the returns have to be removed:

=If([Product]='A', sum(Sales), if([Product]='B', sum(Sales)-sum(Taxes)))

If the text wraps automatically, there is no problem. Hope this helps.

Regards

Re: Variables in .txt file brought in script via include statement

naziralala — Fri, 20 May 2016 22:07:22 GMT

Hello Ellen,

Can you please explain in more detail?

I have done in .txt file..how can we do the same in excel..and in

detail..if possible with an example.

Thanks in advance

Nazira

Re: Variables in .txt file brought in script via include statement

sunny_talwar — Fri, 20 May 2016 23:12:17 GMT

May be check this out: Managing Variables

Re: Variables in .txt file brought in script via include statement

ellenblackwell — Sat, 21 May 2016 00:21:40 GMT

Hello Nazira,

I have used, and would recommend, the same post that Sunny referred to in his latest response. If your requirement can be met using .txt, however, I would try to resolve the issue within your current process.

Depending on the issue, you may just need a 2nd equal sign in your SET statement. Below, I have implemented the 2 suggestions I've made within your expression. Perhaps one of these options will work.

OPTION 1 - Remove hard returns from the expression in your .txt file:

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 ))));

OPTION 2 - Add an '=' sign to the expression, so that it is evaluated when you bring it in

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 ))));

Let us know if one of these work!

Re: Variables in .txt file brought in script via include statement

naziralala — Sun, 05 Jun 2016 20:58:24 GMT

Hello Ellen,

The post recommended by Sunny works except in case as below:

ePYRevenue

'=if (([Travel Year]='$(vYearToday)' and [Travel Month]=$(vMaxMonth)),
sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
if (([Travel Year]='$(vYearToday)'-1 and [Travel Month]=$(vMaxMonth)),
sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
if (([Travel Year]='$(vYearToday)'-2 and [Travel Month]=$(vMaxMonth)),
sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),
sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))))

Please advise.

Thanks for all your help.

Nazira

Re: Variables in .txt file brought in script via include statement

naziralala — Sun, 05 Jun 2016 20:59:28 GMT

Hello Sunny,

The post recommended by you works except in case as below:

ePYRevenue

Please advise.

Nazira

Re: Variables in .txt file brought in script via include statement

Oleg_Troyansky — Sun, 05 Jun 2016 21:14:33 GMT

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

Re: Variables in .txt file brought in script via include statement

naziralala — Sun, 05 Jun 2016 21:43:26 GMT

Hey Oleg,

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

Please advise.

Thanks

Re: Variables in .txt file brought in script via include statement

swuehl — Sun, 05 Jun 2016 22:07:26 GMT

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

Re: Variables in .txt file brought in script via include statement

Oleg_Troyansky — Sun, 05 Jun 2016 22:09:03 GMT

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.