8 Replies Latest reply: Jun 18, 2014 3:41 PM by Josh Abbott RSS

    Include Option

      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
          Stefan Wühl

          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

              It doesn't work.

              It says Unknown option.

                • Re: Include Option
                  Stefan Wühl

                  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

                      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
                          Stefan Wühl

                          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
                              Josh Abbott

                              This seemed to work for me:

                               

                              Load Script.txt now equals:

                               

                              LET TESTVAR = 'United States';

                              or

                              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

                              is

                              OlympicAthletes)
                              where

                              Country

                              ='$(TESTVAR)';

                    • Re: Include Option
                      Josh Abbott

                      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!