Load and use variables from an excel file

[#1]

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:

Variable	Value 1	Value 2
Language	French	English
User	Gestionnaire de bâtiment	Building Manager
Zone	Pièce	Room
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

[ToniKautto]

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)

[#3]

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.

[ToniKautto]

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);