Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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 ...
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;
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
];
Thank you!!