topic Re: Include Option in QlikView

Include Option

Wed, 18 Jun 2014 18:15:10 GMT

Hi,

I have several where statements in my QV dashboard and they keep on changing which means change in script. In my current organization if the dashboard is implemented in server we are not allowed to do so frequent changes in the script. Is there any way I can use include option to write where condition. Attached is a sample data, say I want to filter the data only for United States.

I am aware of using entire script through Include option, however I am looking to pass only where clause through Include option.

Thanks

Re: Include Option

swuehl — Wed, 18 Jun 2014 18:18:37 GMT

I would assume that just inserting a script snippet like a

WHERE Country = 'USA'

by use of INCLUDE should work just fine.

Have you tried it?

Re: Include Option

Wed, 18 Jun 2014 18:30:45 GMT

It doesn't work.

It says Unknown option.

Re: Include Option

swuehl — Wed, 18 Jun 2014 18:51:21 GMT

Right, it seems that INCLUDE only works for complete statements, not for partial ones.

But you should be able to define variables in your script, then load the script before your load and use the variable to expand in your main script:

Variables.qvs

Set vWhereClause = WHERE Country = 'USA'

Script:

$(INCLUDE=Variables.qvs);

LOAD

....

FROM .....

$(vWhereClause)

;

Re: Include Option

joshabbott — Wed, 18 Jun 2014 18:55:52 GMT

If I'm understanding what you are trying to do, you want to have the 'QVW' load the script, and you (Load Script.txt). You can then manually update Load Script.txt to update the where clause, so you don't have to update the QVW every time?

If this is the case, in your script, you only need:

$(Include=C:\Users\deys\Desktop\Data Validation\Qlikview Include Option\Load Script.txt);

In the document you are adding, you have the load script before the include. This will bring in United States, and whatever the include has due to concatenation. Remove the first load statement, and make your script be just this:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

$(Include=C:\Users\deys\Desktop\Data Validation\Qlikview Include Option\Load Script.txt)

;
Run a reload, using the Load Script.txt that you attached. You should only get 'United States' records. Next, update the text file (make sure to update the one this is pointing to on your desktop) and change the Where to be only 'Belgium' and you should only get Belgium records.

Good luck!

Re: Include Option

Wed, 18 Jun 2014 18:56:13 GMT

In this case also any change in my variable condition would through QVW. I would like to avoid any changes through QVW and would like to maintain external file for modifying such conditions.

Re: Include Option

Wed, 18 Jun 2014 19:00:54 GMT

I understand this, however I want to pass only the where condition through text file not the entire script. Is it possible in Qlikview?

Re: Include Option

swuehl — Wed, 18 Jun 2014 19:01:07 GMT

Not sure if I understand. I suggested maintaining the variable definition in the external script.

Then including the variable definitions script file in the QVW.

If you don't use the WHERE clause, create an empty variable.

Re: Include Option

joshabbott — Wed, 18 Jun 2014 19:41:08 GMT

This seemed to work for me:

Load Script.txt now equals:

LET TESTVAR = 'United States';

LET TESTVAR = 'Belgium';

In your qvw load (make sure to update the path):

$(Include=c:\users\me\desktop\include_script.txt)

;

LOAD

Athlete

,
Age

,
Country

,
Year

,
[Closing Ceremony Date]

,
Sport

,
[Gold Medals]

,
[Silver Medals]

,
[Bronze Medals]

,
[Total Medals]

FROM

C:\Users\me\Desktop\OlympicAthletes.xlsx
(ooxml

, embedded

labels

, table

OlympicAthletes)
where

Country

='$(TESTVAR)';