topic Creating more user friendly options in QlikView

Creating more user friendly options

Sat, 26 Nov 2011 19:46:43 GMT

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,

Re: Creating more user friendly options

swuehl — Sat, 26 Nov 2011 20:51:18 GMT

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

];

Re: Creating more user friendly options

Miguel_Angel_Baeyens — Sun, 27 Nov 2011 17:47:50 GMT

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

Creating more user friendly options

rwunderlich — Mon, 28 Nov 2011 05:15:12 GMT

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

Creating more user friendly options

Mon, 28 Nov 2011 22:02:49 GMT

Folks,

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

Cheers