Creating a Dimension from loaded data

[#1]

Hi,

I'm fairly new to QlikView and was wondering how you would script something that could take the values from one field (eg. Country) and create a Dimension

I have loaded the data into the Fact table and then want to create several Dimensions off that Fact table decoding the raw value into something meaningful for the end user

for example

Fact Table has Country as US,UK,AUS,etc- there are approx 150 values in this field

I want to be able to translate UK to United Kingdom, US to United States of America and all other countries to Rest of the World

Anyone can help?

[Oleg_Troyansky]

First, be careful with the terminology - in QlikView, any data field can be used as a Dimension, therefore the term "Dimension" belongs to charts but not to the data structures...

To answer your question - there are several approaches. If you have the "help table" with Country Codes and Names, you can simply load it and either let it stay separate (and linked to the "fact table" via Country Code), or you can "left join" it into the fact table, if it makes sense to you.

My favorite approach is to use a mapping table and "map" your country codes to the names - this way, the task of coding "the rest of the world" is achieved very easily, through the defailt value in function ApplyMap.

Look up "Mapping Load" and "ApplyMap" in the Help section, and you'll se how it's done.

cheers,

[johnw · Accepted solution]

For something as simple as your stated requirement, I would probably add the extra field in the original load using an IF statement. So if your load is something like this:

LOAD
Country
,blah
,blah
,blah
;
SQL SELECT
Country
,blah
,blah
,blah
FROM ...
WHERE ...
;

Just add another field to the load:

,if(Country='US','United States',if(Country='UK','United Kingdom','Rest of the World')) as "Country Group"

Or even just replace your Country definition in the load with that if nobody cares about the difference between Austria and Australia.

The best solution depends a lot on what your fact table load really looks like, what other data you're loading, and so on. For instance, I probably wouldn't nest the IF statement more than a few levels deep before switching to a mapping load. As another for instance, if you're loading from a QVD, using an IF statement switches you from an optimized load to an unoptimized load, which is a big no-no with big data sets. But my default would be to use an IF statement, and then work my way up from there if that isn't up to the task.

[#4]

Thanks for your help