5 Replies Latest reply: Feb 13, 2017 3:12 PM by Rean Fadyl RSS

    How to create a variables spreadsheet and import the variables into Qlik Sense?

    John Blomqvist

      Hi,

       

      Can anyone please show how to create a variables spreadsheet and import the variables into Qlik Sense?

       

      You can do this in QlikView but not sure how to do it in Qlik Sense.

        • Re: How to create a variables spreadsheet and import the variables into Qlik Sense?
          Vladimir Komarov

          John,

           

          I do not think there is much difference between QV and QS in terms of scripting (which covers external spreadsheets load).

          What exactly you are trying to do?

          Personally, I am usually using QVS files to load variables (using "include" statement).

          It's more flexible and allows me to handle special chars like quotes, for example.

           

          Please provide more details about your problem.

           

          Regards,

          Vlad

          • Re: How to create a variables spreadsheet and import the variables into Qlik Sense?
            David Petiot

            Hi John,

             

            I am using a spreadsheet (which i am planning to migrate to our data warehouse solution shortly) to govern dimensions and measures so the definition of the calculations are managed in a central repository.

            To do so, I am using variables, and use a loop to import/create the variables in QS.

            So as long as your spreadsheet has the 2 columns (or more if you need some comments or what the variable does), then you should be able to create the variables.

             

            //Load Measures data from the spreadsheet

            Measures:

            LOAD

                VariableName

                ,Definition

               

            FROM [lib://AppDocumentation/QS_Variables.xlsx]

            (ooxml, embedded labels, table is Measures)

            ;

             

             

            //Loop through to create all Measure Variables

            For i = 0 to NoOfRows('Measures')-1

              Let vVarName = Peek('VariableName',$(i),'Measures');

              Let [$(vVarName)] = Peek('Definition',$(i),'Measures');

            Next

            ;

             

            //Drop Table

            Drop table Measures;

             

            Hope this helps,

            David.

            • Re: How to create a variables spreadsheet and import the variables into Qlik Sense?
              Rean Fadyl

              Hi,

               

              I just created a working example of this for Qlik Sense. This was based on one of the older QlikView postings...

               

              Importing variables from excel

               

              Regards


              Rean

                • Re: How to create a variables spreadsheet and import the variables into Qlik Sense?
                  Oktay Pamuk

                  Here is another example:

                   

                  In every QlikView application, we have expressions, labels, color definitions, variables, etc. Just thinking of the expressions, if we write them in QlikView, every time, we have to change the expressions, we have to change it within each chart.

                   

                  By defining the expressions, labels and colors at one place outside the QlikView application, we are able to manage changes in much more convenient way and even reuse it within other applications. This might be important when using, corporate colors, slogans, titles, etc.

                  I have created an excel file with several sheets, named it GUI_PARAMS.xlsx and put it into the same directory where the QlikView application is. The first sheet contains the sheet names within the excel file, which will be read in to the application and we will call this sheet vSHEETS.

                   

                  Bildschirmfoto 2017-02-04 um 08.35.23

                   

                  Therefore, I have three sheets vCOLOR, vLABEL and vVARIABLES_CALC, which will be processed. I have created each of these sheets with the same names.

                   

                  Bildschirmfoto 2017-02-04 um 08.38.07

                   

                  Each of the sheets containing to columns (column name with the sheet name and the Entry column) containing the variable name and the value.

                   

                  Bildschirmfoto 2017-02-04 um 08.39.34

                   

                  In the script editor of the QlikView application, we can include this code below, which will read in the vSHEETS sheet and then based on the entries on this sheet, read out all other content of the sheets.

                   

                  PARAM_TMP:
                  LOAD vSHEET, Entry
                  FROM [lib://Folder/GUI_Params.xlsx]
                  (ooxml, embedded labels, table is vSHEETS)where not isnull(vSHEET);

                  FOR I = 0 TO NoOfRows('PARAM_TMP') -1
                  vSHEET = PEEK('vSHEET',$(I),'PARAM_TMP');
                  LET $(vSHEET) = PEEK('Entry',$(I),'PARAM_TMP');

                  $($(vSHEET)):
                  LOAD $($(vSHEET)) ,Entry
                  FROM [lib://Folder/GUI_Params.xlsx]
                  (ooxml, embedded labels, table is $($(vSHEET)));

                  FOR I_2 = 0 TO NoOfRows('$($(vSHEET))')
                  vSHEET_2 = PEEK('$($(vSHEET))',$(I_2),'$($(vSHEET))');
                  LET $(vSHEET_2) = PEEK('Entry',$(I_2),'$($(vSHEET))');
                  NEXT I_2

                  DROP TABLES $($(vSHEET));

                  NEXT I
                  DROP TABLES PARAM_TMP;

                  When you run the script, you will see the entries in the Variable Overview (under Settings).


                  Regards,

                   

                  Oktay

                  www.qlik-blog.com

                • Re: How to create a variables spreadsheet and import the variables into Qlik Sense?
                  Rean Fadyl

                  And also there is the Qlik Deployment Framework which provides for storing variables in .txt files and incorperating these into your app...

                   

                  Qlik Deployment Framework