3 Replies Latest reply: Nov 21, 2011 3:04 PM by Toni Kautto RSS

    Load and use variables from an excel file

      Hi,

       

      I would like to have my QV doc available in different languages. The choice of the manguage would be available in a multibox (up to now, there is no problem).

       

      But I don't want to duplicate every sheet, so I want to use variables in my different pages for text, and then these variables would take the value corresponding to the chosen language.

       

      For example, I would have a variable "DataSelection", and it would take the value "Sélection de données" in French or "Selection Of Data" (not very good but it is just to distinguish it from the variable name) in English.

       

      So I created a table on excel with:

      VariableValue 1Value 2
      LanguageFrenchEnglish
      UserGestionnaire de bâtimentBuilding Manager
      ZonePièceRoom
      etc...

       

      My question is:

       

      How do I do to load it in order then that as soon as I choose French, every variable in column 1 take the value on column 2 (value1)?

       

      (in the script or in expressions)

       

      Many thanks in advance,

       

      benetche

        • Re: Load and use variables from an excel file
          Toni Kautto

          You should load it in to a table with the field Language, Variable and Value, so that by a language selection you only have the values for that language available. This should be possible to accomplish with the tranformation features like Transpose in the table load wizard.

           

          In the application you can then pick the string with a set expressions like

          =only({$<Variable={'VariableValueToLookup'}>} Value)

            • Load and use variables from an excel file

              Cool, it works fine.

               

              But this is not exaclty the good method in my opinion since it would be way better if I only had to add a new column for a new language in my excel table.

               

              Anyway, works for what I'm doing now and the only 2 languages I want to translate into.

               

              Thanks.

                • Re: Load and use variables from an excel file
                  Toni Kautto

                  I agree, for maintenance you should keep your original structure. This is also what I meant by transforming the data during load by using the QlikView tranformation features. Sorry for mixing things up, you should use the Cross Table feature to twist the sheet data. Find principle steps below, and sample files.

                   

                  1. Desktop client > Script Editor > Tables Files...

                  2. Locate your excel file

                  3. Press next to go to Transform dialog

                  4. Enable Tranformation Step

                  5. Press next to reach the Options dialog

                  6. Press Crosstable...

                  7. Name the Attribute field to Language and the Data field to Value

                  8. Press Finish to finalise the load and you will get something like below in your script

                   

                  CrossTable(Language, Data)

                  LOAD

                        Variable,

                       French,

                       English

                  FROM Book1.xlsx (ooxml, embedded labels, table is Sheet1);