Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
Employee
Employee

A colleague of mine had translated two demos from the Demo Site to Japanese and wanted to know if we could post them on the Demo Site alongside the English versions. We decided that it would be best to combine the English and Japanese versions into one multilingual Qlik Sense app making it easier for us to add additional languages to the app as needed. It was an easy process and required only a few steps:

  1. Create a translation sheet with all the languages that will be available in the app
  2. Update the script to add a table of the translations and a list of the languages available in the app
  3. Add a Language filter pane to every sheet in the app that allows only one selected value
  4. Update sheet names, chart titles, subtitles and labels with an expression that will display the text in the selected language

Create Translation Sheet

To begin the process of making a demo multilingual, I created an Excel file with all the languages that are to be included in the app. Below is a snippet of the worksheet. The first column, Index, has a unique value which will be used in the charts and expressions to indicate what data should be displayed. The second and third columns are the languages to be used in the app. An additional column can be added for additional languages that need to be added to the app. In this scenario, I entered all the English text (sheet names, chart titles and subtitles, labels and text) and then using the Japanese version of the app, I entered the respective Japanese text. If I did not have the Japanese version of the app, I would have shared the Excel file with someone who could enter the Japanese translations for me. Preparing the Excel file in this format makes it easy to add additional languages to the app without having to update the QVF.

Snippet of Excel translation sheetSnippet of Excel translation sheet

Update the Script

Once the translation sheet was created, it needed to be loaded into the data model. The script below is what I added to the demo.

script.png

On line 1, the HidePrefix system variable is used to hide all fields that begin with “#.” Starting on line 3, the Excel file is loaded. Once it is loaded, the vLanguage variable is set to the expression “=Minstring(#LANGUAGE).” This is an important step and we will take a closer look at this when we update the front-end. On line 13, the languages from the Excel file are loaded - users can select the language they would like to view from this list. These languages are then stored in the #LANGUAGE field which will be hidden from the user (since it starts with “#”).

Add Language Filter

One each sheet in the app, I added a Language filter pane using the dimension #LANGUAGE that was created in the script. Once the script is reloaded with the HidePrefix variable, the #LANGUAGE field will not be visible, but you can still use it as the dimension in the Language filter pane. I needed to see the field temporarily so I commented out the HidePrefix line in the script and reloaded so I could change a setting on the field. I only want the user to be able to select one Language at a time, so I needed to check the “Always one selected value” checkbox in the Field settings of the #LANGUAGE field. (Right click on the #LANGUAGE field and select Field settings to see the window below).

Field settings dialog windowField settings dialog window

Once my settings are saved, I uncommented the HidePrefix line and reloaded the app to hide the #LANGUAGE field again. The filter pane will look like this (image below) and only one language can be selected at any given time. When a language is selected, the vLanguage variable (that was created in the script) will store the language. This variable is used later when updating the text in the UI.

Language filter paneLanguage filter pane

Update Front-End

Now the last step is to update everything in the app that should be translated. In this scenario, I updated sheet names, chart titles and subtitles, chart labels, KPI text and text on the sheets. Here is an example of how I updated the title of the Language filter pane. In the title field, I entered:

index.png

In the snippet below from the Excel translation sheet, the Index is 64 for the text “Language” which is why I used it in the expression above for the title of the Language filter pane. This expression will return either the English or Japanese translation for Language depending on the value of the variable vLanguage.

Snippet from Excel translation sheetSnippet from Excel translation sheet

Another piece of information I would like to share is how I handled Text & image objects that needed to be translated. In the screenshot below, there is heading text and body text that have 2 different formats (font size and font color).

intro.png

To handle this, I created two variables, one for the heading and one for the body and in the variables, I stored the translation expression.

edit intro.png

variables.png

This way, I was able to not only translate the text but format the text in a single Text & image chart two different ways.

As you can see, it is easy to make a Qlik Sense app multilingual and it is easy to update the app with additional languages as needed. Sales, Customer Experience & Churn and Supply Chain – Inventory & Product Availability are the two demos that were made multilingual. Check them out and switch between the languages to see the final results. If you are interested in doing this in QlikView, check out Chuck Bannon’s blog on this topic as well as making the data multilingual in a QlikView app.

Thanks,

Jennell

8 Comments
Contributor II
Contributor II

This is amazing. When you are specifically in a diverse country, this will help a lot. 

0 Likes
3,209 Views
Contributor II
Contributor II

Is it possible to translate the actual data?
e.g. one Dimension is a list of countries. Is it possible to translate the dimension data(=the country name) itself?

0 Likes
2,528 Views
Employee
Employee

Yes, it is possible to translate the actual data too.  Please refer to the blog link I included in my post - Chuck Bannon wrote a blog titled Handling Multiple Languages in QlikView which discusses how to do this.  The same process can be done in Qlik Sense.

2,502 Views
Contributor II
Contributor II

How does  the formula

=Only({<Index = {48}>} [$(vLanguage)])

referencing the Translations table in order to get the correct value?I don't see it referenced in the formula and it's not joined to the data.

When I attempt to implement this in a test situation, I can see that the formula is evaluating as 

=Only({<Index = {48}>} [MinString(%Language)])

but right above that is the message "Error in expression" and I get a blank Text & image visualization or a null value in a Title field for my filter pane.

 

We're running April 2019, Patch 3 if that would make any difference.

0 Likes
2,399 Views
Employee
Employee

Hi Mljester,

In the Translation table, there is a field for Index, English and Japanese.  In the expression, vLanguage is a variable that stores the selected language which in this case is either English or Japanese.  The expression uses set analysis to select the text when Index = 48.  In the image below, I have selected 48 and depending what language is selected the respective text will be displayed.  

example.png

When building the translation sheet, I assigned each bit of text (chart title, sheet name, etc) to an index and use that to display the text in the correct place.  You are correct, the Translation table is not associated to the other data in the data model but it does not need to be in this example since I am not translating the actual data but rather just the sheet names, chart titles, labels, etc.

I hope this helps.

Jennell

0 Likes
2,354 Views
Contributor II
Contributor II

Thanks Jennell

I appreciate the explanation. I was able to get it to work, but I had to change the formula for the fields slightly in order for it to work. My final formula turned out to be

=Only({<Index = {48}>} [$(=%Language)])

If I didn't change the formula to be the actual field instead of the variable, I got only errors.

2,348 Views