topic AW:Re: Dynamic translation not possible at 100%? in QlikView

Dynamic translation not possible at 100%?

Thu, 20 Jan 2011 16:10:35 GMT

Hi Everyone,

I have already checked on the "Finance Controlling" applications to see how to make my qvw document multi-lingual but I have still 2 situations where I can't find a way to traduce.

I explain:

1) Pivot Tables:

In my pivot tables, I can see the names of my fields but they are not multi-lingual. How can I get them translated dynamicaly?

2) In a Bar chart:

My legend is composed of the content of the field of one of my dimension. But how can I get them translated dynamicaly?

Thank you very much for your help 🙂

Dynamic translation not possible at 100%?

Thu, 20 Jan 2011 16:28:42 GMT

Hello, The way i do multilanguage is:

I have a table in the database with 3 columns: Language, TextToTranslate, Translation

I import this into the Qlikview file as a standalone table.

Then i have a listbox with the Language field. The listbox has the "Always one selected value" set under the listbox properties. (You need to select one value before u can set that property)

Then everywhere for labels, captions, dimensions et.c. i do this

MAXSTRING(IF(TextToTranslate = 'Customer', Translation, ''))

So for example if the table looks like this:

Language, TextToTranslate, Translation

English, Customer, Customer

Swedish, Customer, Kund

Norwegian, Customer, Kunde

And yuo have selected Swedish in the listbox then the result of

=MAXSTRING(IF(TextToTranslate = 'Customer', Translation, '')) will be Kund

Cheers

Martin

Dynamic translation not possible at 100%?

Thu, 20 Jan 2011 16:50:45 GMT

Hi Martin,

Thanks for the quick reply but What I really don't know is:

where can I put this code for the labels of the field (so, I mean each titles of each colums) in a pivot table. Like in the following image for the translation of Corporate_Id:

and where can I put this code for the legend of my bar chart (because if I put this on the label, then I get the only one label on top of all my possible values for the legend). Like in the following chart:

Thank you,

Caro.

Dynamic translation not possible at 100%?

Thu, 20 Jan 2011 16:59:36 GMT

The code goes where it says Label for the dimensions and expressions.

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 10:26:12 GMT

Hi Martin,

I know about these places and I have tried and used these for expresions and some dimensions but in this case it is not what I am looking for.

You can look at the picture below. What you are suggesting is the "global" legend label. But I would like to traduce each field of the legend.

As you see here, when we use the label, we get a global title for the legend (here: Produit & Services). But I would like to translate the ones below that (Product & Services, Means of payment processing, local electronic banking...etc.)

Best Regards,

Caro.

AW:Re: Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 12:13:03 GMT

Hello Caro,


Caro wrote:As you see here, when we use the label, we get a global title for the legend (here: Produit & Services). But I would like to translate the ones below that (Product & Services, Means of payment processing, local electronic banking...etc.)

The Label is Produit & Services. But the others below are values (member of your dim called Produit & Services) and come directly from the (database-)field belonging to the dimension. So if you want to translate them, you have to do it during the load. OK?

Regards, Roland

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 12:17:58 GMT

You use the same the approach as i mentioned before.

Instead of using a field you need to use calculated dimension

So if your field is Rev_rubric then it should be something like

=MAXSTRING(IF(TextToTranslate = Rev_rubric , Translation, ''))

All values for Rev_rubric need to be in the translation table.

Cheers

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 12:48:45 GMT

Oooops, didn't work as expected. Attached is a working solution. Cheers Martin

AW:Re: Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 14:13:10 GMT

Hi Roland,

Yes, it is as you said the members of my dimension 😉

I have seen some other post about this solution but nothing quiety clear for me.. 😞 sorry I am relly just beginning with the QlikView coding.

Do you have any references to do it step by step?

One of the big doubt I still have with translation at load time is the following:

If in the qvw application I have buttons to change the language, would my label then be tranlated too? or is it just working for application when you chooses the language at start/load?

Thanks,

Caro.

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 14:14:09 GMT

Hi Martin,

Again thank you for taking the time to answer me.

I can't find you attachement 😉

Caro.

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 15:54:01 GMT

Trying to send the attachment again.

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 16:09:03 GMT

To add data into the translation table from field values you could do:

-- Create the translation table

CREATE

TABLE [dbo].

[TranslationTable]

(

[Language]

NVARCHAR(50),

[TextToTranslate]

NVARCHAR(50),

[Translation]

NVARCHAR(50),

)

-- Insert values from a field for first language

INSERT

INTO

[TranslationTable]

(

[Language]

[TextToTranslate]

[Translation]

)

SELECT

DISTINCT

'English',

[YourField]

FROM

[YourTable]

WHERE

LTRIM(RTRIM(ISNULL([YourField] , ''))) <> ''

-- Insert values from a field for second language (to be translated)

INSERT

INTO

[TranslationTable]

(

[Language]

[TextToTranslate]

[Translation]

)

SELECT

DISTINCT

'Swedish',

[YourField]

FROM

[YourTable]

WHERE

LTRIM(RTRIM(ISNULL([YourField] , ''))) <> ''

-- Then go in and translate the rows for the second language in Management Studio (for MS SQL)

SELECT

* FROM [TranslationTable] WHERE [Language] = 'Swedish' AND [TextToTranslate] = [Translation] ORDER BY[TextToTranslate]

Dynamic translation not possible at 100%?

Fri, 21 Jan 2011 16:59:16 GMT

Yet another version with some more objects translated.

AW:Re: Dynamic translation not possible at 100%?

Tue, 25 Jan 2011 16:24:20 GMT

Hi Roland,

I would like to make the mapping load but I don't know how to do it..

I have made some researches and tests but I can't get it working. Here is what I have in my script:

FieldsMapFR:
Mapping LOAD * INLINE [
REV_SubRubric, REV_SubRubric_FR
Means of payment processing, Moyen de traitement des paiements
Product & Services, Produit & Services
Local Electronic banking, Banque Local Electronique
Float, Flottement
Interest Margin, "Marge d'intérêts"
BTS, BTS
];

MAP Revenues using FieldsMapFR;

Revenues:
LOAD Corporate_Id,
Rubric as REV_Rubric,
[Sub-Rubric] as REV_SubRubric,
Product as REV_Product,
YYYYQQ as REV_YYYYQQ,
Revenues
FROM
[Data\Facts Tables\REV_Facts.xlsx]
(ooxml, embedded labels, table is REV_Facts) WHERE 1=1;

REV_SubRubric is the field where my values are. Like in the following example:

Corporate_Id	Rubric	Sub-Rubric	Product	Year	Quarter	YYYYQQ	Revenues
X	F&C	Means of payment processing	Domestic Transfers	2009	Q1	2009Q1	17511
X	F&C	Local Electronic banking	Other electronic banking	2009	Q1	2009Q1	68
X	IM, Float & BTS	Float	Float	2009	Q1	2009Q1	14806
X	IM, Float & BTS	Interest Margin	Interest Margin	2009	Q1	2009Q1	246320
X	IM, Float & BTS	BTS	Revenues on LORI account	2009	Q1	2009Q1	341
X	IM, Float & BTS	BTS	Transfer fees	2009	Q1	2009Q1	1185

Now, I don't know how to use that for my chart. Could you help me with that?

Is that going to work at runtime when user change its language?

If this works, does that mean that I need to do a map table for each language I want to be available?

Thanks for your help,

Best Regards,

Caro.

AW:Re: Dynamic translation not possible at 100%?

Tue, 25 Jan 2011 17:15:32 GMT

Hi again Roland,

I finally managed a solution. In my load script I have now the following:

Revenues:
LOAD Corporate_Id,
Rubric as REV_Rubric,
[Sub-Rubric] as REV_SubRubric,
Product as REV_Product,
YYYYQQ as REV_YYYYQQ,
Revenues
FROM
[Data\Facts Tables\REV_Facts.xlsx]
(ooxml, embedded labels, table is REV_Facts);

FieldsMap1:
LOAD * INLINE [
REV_SubRubric, REV_SubRubric_FR, REV_SubRubric_DE
Means of payment processing, Moyen de traitement de paiements, Means of payment processing
Product & Services, Produit & Services, Product & Services
Local Electronic banking, Banque électronique locale, Local Electronic banking
Float, Flottement, Float
Interest Margin, "Marge d'intérêts", Interest Margin
BTS, BTS, BTS
];

which give me the following tables:

Then I changed my REV_SubRubric dimension in a calculated dimension like this one:

=If(GetFieldSelections(Language_Id) = 'FR', REV_SubRubric_FR, (If(GetFieldSelections(Language_Id) = 'DE', REV_SubRubric_DE,REV_SubRubric)))

And it worked.. 🙂

Do you think that this is a good way of programming? If you have any other better solution I am still open to it 🙂

Thanks,

Caro.