3 Replies Latest reply: Feb 13, 2017 5:33 AM by Oktay Pamuk RSS

    Importing variables list from Qlikview in Qlik Sense

    Sebastian Schmidt

      Dear Community,

       

      I'd like to visualize an app from Qlikview in Qlik Sense. I already imported the qvw file. But still the variable list from Qlikview is missing. Do I have to create every single variable (they are a lot!) or is there a better option like importing the variable list? Maybe with importing an Excel file?

       

      Greetings

      Sebastian

        • Re: Importing variables list from Qlikview in Qlik Sense
          Bill Markham

          I always create variable in the load script or store them in external files.

           

          It sounds like your variables have been created in the GUI and you describe one of the many reasons I do not like this method of creating variables.

           

          This blog post may give you inspiration Importing and exporting variables - The Qlik Fix! The Qlik Fix!

          • Re: Importing variables list from Qlikview in Qlik Sense
            Jonas Hertz

            One quick way to get your QlikView variables out of your document is to create an Input Box object where you then add all your variables. Once all variables are in the Input Box right click and choose to export to Excel (or copy to Clipboard).


            Next you need add your variables to your Qlik Sense application and there are a number of ways to achieve this, for example see these blog posts:


            Storing variables outside of QlikView - The Qlik Fix! The Qlik Fix!

            QlikView Addict: Populate QlikView variables from an external file

              • Re: Importing variables list from Qlikview in Qlik Sense
                Oktay Pamuk

                Once, created the excel I would recommend to make a resuable Excel sheet, where you can read the variables into many applications. Here is an 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