4 Replies Latest reply: Nov 28, 2011 5:02 PM by Alan Hendrickx RSS

    Creating more user friendly options

      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

        • Re: Creating more user friendly options
          Stefan Wühl

          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

          ];

            • Re: Creating more user friendly options
              Miguel Angel Baeyens de Arce

              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
                Rob Wunderlich

                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

                Folks,

                 

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

                 

                Cheers

                A