Creating more user friendly options

[#1]

Hi All,

I am just starting out on Qlikview and have had a reasonable amount of success so far in so far. I do find myself getting stuck every so often with what I know are simple tasks, but that's to be expected. I have been able to figure most things out by looking over the training material and other examples however I have one issue which I hope the community might be able to help me with......please 🙂

I am working with an excel worksheet which contains a column called 'region'. The values within this column are single letters. To improve usability I would like to change my Qlikview application to display the full region names without changing the underlying data.

In plain english, I want to say display 'East' instead of 'e' etc.

Can one of you kind people please tell me how this is achieved? Is is a change at the load stage or can this be done for individual objects?

Many thanks,

A

[swuehl]

Hi hendrickx,

you could use a mapping table to map your single letter region field to a full name region field, like below. The REGIONMAP table is only existing during load, the applymap functions queries the full name values from it. As a result, you get an table, INPUT, with the original field Region and an additional field RegionName. The Inline table is only for demonstration, you probably replace it just with a ... from YourDatabaseTable;

Instead of mapping the Name into the Table as a new field, you could remove the line with the applymap() and the Mapping prefix, and thus create a second resident table that is linked via Region to your Input table.

In any object, you can now use the RegionName to have a more user friendly list of values, e.g. in a selection box or as dimension.

Hope this helps,

Stefan

REGIONSMAP:

Mapping LOAD * INLINE [

Region, Name

W, West

N, North

S, South

E, East

];

INPUT:

LOAD

*,

applymap('REGIONSMAP',Region) as RegionName

INLINE [

Region

E

W

N

S

];

[Miguel_Angel_Baeyens]

Hi,

I agree with Stefan's solution, you can check this one also and see how they perform should you have a large data volume to load, and probably easier since this doesn't need any additional table

Data:
LOAD ...,
     Pick(Match(Region, 'N', 'S', 'E', 'W'), 'Reg. North', 'Reg. South', 'Reg. East', 'Reg. West') AS FriendlyRegionName
FROM Source;

Match() function will return a numeric value when the match is done (1 for 'N', 2 for 'S' and so) and Pick will take the result of this match from the list of expression following.

By the way, I would always do this kind of stuff in the script rather than in the objects using expressions. The latter can slow down your redering time.

Hope that helps.

Miguel

[rwunderlich]

If you don't need both the code and the name -- that is, you just want to expand the code out, I find a simpler form to be:

REGIONSMAP:

Mapping LOAD * INLINE [

Region, Name

W, West

N, North

S, South

E, East

];

MAP Region using REGIONSMAP;

-Rob

[#5]

Folks,

Thanks for the very helpful responses - everything is working as required!

Cheers

A