topic Re: Translation table in QlikView

Translation table

Mon, 30 Mar 2015 11:40:28 GMT

Hi All,

I have the following "translation" table in my DB:

make	model	real make	real model
104	6022	Sam	5
179	Sams	Sam	5
421	05CC	-	-
421	00AA	-	-
650	Sa	Sam	4
660	Sams	Sam	5
770	S	Sam	-
880	Sa	Sam	-

I also loading another table that contains the following fields:

Activities:

load

Activity ID

Make

Model;

I want to check in the script if I the ID has make&model values that appears in the translation table. if it has, I want to change the make&model values to the "real make" and "real model". for example, If I have an ID ="104" and make= "6022" as model, I want to change it to the the real make (sam) and to the real model (5) like in my translation table.

I thought to use apply map but this function works only with one value right?

Re: Translation table

Gysbert_Wassenaar — Mon, 30 Mar 2015 11:45:26 GMT

Yes, only one field can be the key, but you can combine make and model to create a new key field:

MapModel:

MAPPING LOAD

make & '|' & model as Key,

real_make_substitute_value & '|' & real_model_substitute_value as Value,

FROM ....

Data:

LOAD

make,

model,

subfield(applymap( make & '|' & model), '|',1) as real_make,

subfield(applymap( make & '|' & model), '|',-1) as real_model

FROM ...

Re: Translation table

anbu1984 — Mon, 30 Mar 2015 11:51:32 GMT

TranslationTbl:

Mapping Load Trim(make) & '|' Trim(model), RealMake & '|' & RealModel;

Select * From TranslationTable;

Activities:

Load *, Substring(RealMkMdl,'|',1) As RealMake, Substring(RealMkMdl,'|',2) As RealModel;

Load

Activity ID,

Make,

Model,

Applymap('TranslationTbl',Trim(Make) & '|' Trim(Model),'Default value') As RealMkMdl;

Re: Translation table

maxgro — Mon, 30 Mar 2015 11:52:12 GMT

map:

Mapping load

make & '|' & model as from,

[real make] & '|' & [real model] as to;

LOAD make,

model,

[real make],

[real model]

FROM

[http://community.qlik.com/thread/158128]

(html, codepage is 1252, embedded labels, table is @1);

Activities:

load

[Activity ID],

subfield(ApplyMap('map', make & '|' & model, make & '|' & model), '|', 1) as make,

subfield(ApplyMap('map', make & '|' & model, make & '|' & model), '|', 2) as model;

load * inline [

Activity ID, make, model

1, 104, 6022

2, 880, Sa

3,a,b

4,c,d

5,179,Sams

];

Re: Translation table

Mon, 30 Mar 2015 11:52:39 GMT

Thank you!!