Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Translation table

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?

4 Replies
MVP & Luminary
MVP & Luminary

Re: Translation table

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


talk is cheap, supply exceeds demand
anbu1984
Honored Contributor III

Re: Translation table

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;

MVP
MVP

Re: Translation table

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

];

Not applicable

Re: Translation table

Thank you!!