topic Load Script Question in QlikView

Load Script Question

Tue, 08 Feb 2011 00:18:57 GMT

Hi guys,

I am hoping someone can help me out with this as I am relatively new to QlikView. I have two tables.

The names of the fields in the table are different, but content wise they are related. The small table is like a lookup table with the following fields:

Field, Value, Desc

Gender M Male

Gender F Female

Car B BMW

Car Me Mercedez

In my main table I have a field called Gender and Car and the fields have only the values in it, like M, F, B and Me as above, but not the description.

For the purpose of the app, I am required to use the descriptions in the app. How can I relate the two tables together in my app?

Thank you

Load Script Question

johnw — Tue, 08 Feb 2011 01:15:46 GMT

Well, here's one way. I don't like it because you need to write separate code for each value of "Field". It wouldn't be too difficult to convert it to a loop for all "Field"s if you have a lot, but even then the processing would be slow. It seems like there might be some clever solution using "generic load", but I failed to find it quickly. I figured a dirty solution is better than no solution, so here you go in case nobody comes up with something better.

// Read in your generic lookup table.

[Lookup Table]:
LOAD * INLINE [
Field, Value, Desc
Gender, M, Male
Gender, F, Female
Car, B, BMW
Car, Me, Mercedez
];

// Convert to maps.

[Gender Map]: MAPPING LOAD Value, Desc RESIDENT [Lookup Table] WHERE Field = 'Gender';
[Car Map]: MAPPING LOAD Value, Desc RESIDENT [Lookup Table] WHERE Field = 'Car';

// Tell it to use the maps you prepared.

MAP Gender USING [Gender Map];
MAP Car USING [Car Map];

// Now when you read your data, it will map the codes and store the descriptions instead.

[Data]:
LOAD * INLINE [
Gender, Car, Sale Price
M, B, 40000
M, Me, 30000
F, B, 38000
F, Me, 35000
];

// Drop the original generic lookup table. Mapping tables are dropped automatically at the end of the script.

DROP TABLE [Lookup Table];

Load Script Question

Tue, 08 Feb 2011 20:37:44 GMT

Thanks John