topic Re: Language translation for a single column in source data in QlikView

Language translation for a single column in source data

Wed, 11 Nov 2015 09:52:34 GMT

Hi All,

I have a data source with only 1 column in German( column name and column values in German) while all the other columns and data are in english.

I have a separate data dictionary file with mapping translation values for the data in German.

How do i translate the data in German into english using qlikview.

Example:

Source data file:

A B C Column_GR

1 abc 10 <Germanword1>

2 aba 20 <Germanword2>

3 abs 30 <Germanword1>

4 gbx 10 <Germanword3>

5 fbz 40 <Germanword2>

Data Dictionary

Column_FR Column_EN Column_ENDescription

<Germanword1> <English word1> Description of the word1

<Germanword2> <English word2> Description of the word2

<Germanword3> <English word3> Description of the word3

Required result:

A B C Column_GR Column_EN Column_ENDescription

1 abc 10 <Germanword1> <English word1> Description of the word1

2 aba 20 <Germanword2> <English word2> Description of the word2

3 abs 30 <Germanword1> <English word1> Description of the word1

4 gbx 10 <Germanword3> <English word3> Description of the word3

5 fbz 40 <Germanword2> <English word2> Description of the word2

Am i correct in saying that join has to be used? If yes, please guide me on how it has to be done. Thanks!

Re: Language translation for a single column in source data

marcus_sommer — Wed, 11 Nov 2015 10:43:53 GMT

A (left) join is a possible solution but you need to be careful not to duplicate records or filter them out (by inner). An alternatively is mapping, see: Mapping … and not the geographical kind

- Marcus

Re: Language translation for a single column in source data

Thu, 12 Nov 2015 01:40:43 GMT

Thank you for your reply. Mapping table is a good idea. But the problem i face is mapping table can accept only 2 columns while my data dictionary has 3 columns.

So is it advisable to create as 2 mapping tables? one with Column_FR , Column_EN and the other with Column_FR, Column_ENDescription ... where Column_FR can be the lookup field (also present in source data file) ??

Re: Language translation for a single column in source data

marcus_sommer — Thu, 12 Nov 2015 07:47:07 GMT

You could simply use two mappings. Mapping is usually very fast so that's no problem to apply several mappings at the same time. Alternatively you could merge those fields like: Field1 & '|' & Field2 as FieldMatching and then use a subfield-function around the applymap to split it again:

...

subfield(applymap('Maptable', Field), '|', 1) as Field1,

subfield(applymap('Maptable', Field), '|', 2) as Field2

....

- Marcus

Re: Language translation for a single column in source data

Mark_Little — Thu, 12 Nov 2015 08:24:49 GMT

Hi,

I would go the same way as Marcus on this. I used them al the time. Just a little more information.

I would approach as below

Table1:

Load

From 'Your file'

Map1:

Mapping Load 1

Column_FR,

Column_EN

Resident Table1;

Map2:

Mapping Load 1

Column_FR,

Column_ENDescription

Resident Table1;

Drop Table Table1;

Then apply the maps in the needed table.

Mark

Re: Language translation for a single column in source data

Thu, 12 Nov 2015 09:48:16 GMT

Thank you for additional information. It helps!