Multi Lingual Application - Dynamic Data Solution

[thairshans12]

Hey Qlik Community need some advice on how to resolve this  Qlik Data challenge. 

So I have a spreadsheet, that is being used to translate English to multiple languages. The Multi Lingual App works:

However here's the issue, the formula I am using in the front end of my app is the following:

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

This is the data load editor script:

Translations:

LOAD

    "Index",

    English,

    Koreon,

    Spanish,

    German,

    French,

    Italy

FROM ........xlsx]

(ooxml, embedded labels, table is Sheet2);

LET vLanguage = 'Minstring(#LANGUAGE)';

 Language:

 Crosstable (Language,Translation) Load 

  Index,

    English,

    Koreon,

    Spanish,

    German,

    French,

Italy

Resident Translations; 

Language List:

Load Distinct Language as #LANGUAGE

RESIDENT Language 

WHERE Language > 0;

Drop Table Language;

The Translation spreadsheet has the following columns:

The index field is a unique identifiable numbers linked to each term throughout my app, so for instance 'Retail Sales', 'Conversions', 'KPI Data' for Chart Titles/Labels and Dimensions. 

For example:

Index  | English | Koreon

6 | KPI Data 

8 | Conversions 

Now Qlik Community, my question is say the Spreadsheet grows to 4000 Rows, Id like to avoid manually using formulas to match the index numbers with the only function.

Front end:

Place in chart Label or Title:

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

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

Is there a dynamic function available that I can use instead of Only as my dimensions may have  4000 Rows of data?

Thank You so much! 

[Or]

I tried following along, but I'm not quite sure what it is you're asking:

"Is there a dynamic function available that I can use instead of Only as my dimensions may have 4000 Rows of data?"

What exactly is this dynamic function supposed to do? As I understand your post, it has no way of guessing which of your 4000 rows you mean in any given expression, so you'd always need to spell it out - which is where the set analysis comes in, and thus the Only() function. If you'd rather not spell it out with an index number, you can instead refer to e.g. the English version by using {< English = {'Conversions'} >}  instead of Index = 8, but this is no more dynamic than the original, just easier to read.

Perhaps I misunderstood what you're trying to achieve, in which case let me know...

[thairshans12]

Hey so with the only function I have to manually insert the index numbers 1/2/3/4/5 etc 

But if my dimensions have 4000  rows so index will be 1 - 4000 numbers is there a dynamic way of picking the index number up in the only function?

Hope that's clearer? 

Thank you 

[thairshans12]

So In the spreadsheet:

The front end will have the following Formula:

Here I am manually inserting the index numbers in the only function, so imagine I have a data set with a dimension of 4000 rows it will take me long time to update the values within the brackets of the index number. 

So is there a more dynamic way of doing this.

Thank you in advance!

[Or]

As I mentioned in my previous post, I don't quite understand how you'd expect this to be dynamic. Is there some way for Qlik to guess which particular index you want in a given expression? Is this based on any sort of logic other than the developer knowing what they're referring to?

As a shortcut, you could add a column to your Excel which pre-generates the formula for that index and then copy-paste that instead of changing the index by hand, but this wouldn't be that much better than copying the base and changing the index. Maybe a little less error-prone, though.

Perhaps unrelated, but do you really have 4000 different texts requiring translation in your app? This seems like an excessively large number to me. When creating multi-language apps in the past, I've never had more than a few hundreds and typically less.

[thairshans12]

So theres no guessing, the index number refer to Chart titles in place of the application. 

I have used Index numbers as an ID value which refers to the english Title text being displayed. 

Yeah so we have a larger amount of Product data which would be nice to dynamically translate in qlik. Otherwise Id need to use a formula in Excel. Then apply manually, if it cant be done dynamically. 

If anyone has done this in the past or now, would appreciate your thoughts.

Thank you btw for your advice. 

[Or]

---

@thairshans12 wrote:

So theres no guessing, the index number refer to Chart titles in place of the application. 

---

I understand this, and the developer knows this, but how would you expect Qlik to know that this particular chart title happens to be index #42? Is this in some way inherent to the chart? If so, there might be ways to shortcut the process, though they'd still be pretty limited. 

[arieidel79]

I understand that there are two different aspect of the multilanguage configuration:

1. Sheet titles, Charts and columns titles, dimension names, etc.
2. Data (e.g. Transactional data coming from a data base)

Implementing an Excel file by indexing each text with its corresponding translation and including the set analysis with the Only() function to put the corresponding translated text, I think this is covered by the first aspect.

But I think its been asked about translating "Product data", which I see it related to the second aspect mentioned. In this case, I guess that a "Product" table exists in a database (which is a data source from the Qlik app), it might have an ID field and a "Name" field, which values they want to translate. And it seems that they have 4000 products (rows) in this table so they are 4000 Product names to be translated. If this is the case, I think a new column/field should be created in the "Product" table for each language. So, for example, the current "Name" column could have the product name in English and a new "Name_Spanish" column could be created to put the product name in Spanish and so on. Then, instead of dynamically refer to a cell in an Excel file, you should refer to a specific field to get the product name for the selected language.