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