Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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) ??
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
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
Thank you for additional information. It helps!