Translation table

[#1]

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?

[Gysbert_Wassenaar]

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 ...

[anbu1984]

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;

[maxgro]

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

];

[#5]

Thank you!!