Language translation for a single column in source data

[#1]

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!

[marcus_sommer]

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

[#3]

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

[marcus_sommer · Accepted solution]

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

[Mark_Little]

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

[#6]

Thank you for additional information. It helps!