5 Replies Latest reply: Aug 10, 2014 12:59 AM by Josh Good RSS

    How to group to a new dimension?

      My dear friends,

       

      If I have a field called “city” in my source data and there are city A, B, C, D, E, F, G under it. I want to group city A and B to “east”, group C and D, E to “middle”, group F and G to “west”. And want “east”, “ middle”, “west” under a new field called “REGION”.

       

      Can this be achieved in Qlik sense? How shall I do? Many thanks!

       

      Regards,

      Edward

        • Re: How to group to a new dimension?
          Michael Tarallo

          Hi Edward,

           

          Yes it can - this can be done using the expression syntax defined in the load script - or better yet defined in a new dimension. I will attach a sample app for you shortly.

           

          Mike

          • Re: How to group to a new dimension?
            Michael Tarallo

            See attached .qvf file - copy to C:\Users\<your user>\Documents\Qlik\Sense\Apps and restart Sense Desktop.

             

            (NOTE: The LOAD Script contains sample script that is used ONLY to generate sample data for your example.
            You can do this in any app by pressing the key combination CTRL + 0 + 0 while in the Data Load Editor)

             

            Open Buckets App.

             

            capture4.PNG.png

             

            Better defined as a Dimension in the app, you can used this syntax:

             

            Here is a simple one:

             

            if(match(Alpha,'A','B','C','D'),'East','West')

             

            Here is a nested one:

             

            if(match(Alpha,'A','B','C','D'),'East',if(match(Alpha,'E','F','G','H'),'West','South'))

             

            (you could also use this in the load script if you wanted to like this:

             

            if(match(Alpha,'A','B','C','D'),'East',if(match(Alpha,'E','F','G','H'),'West','South')) as Region Bucket

             

            Capture.PNG.png

            Capture2.PNG.png

            capture3.PNG.png

             

            HTH

             

            Mike T

            Qlik

            • Re: How to group to a new dimension?

              Hi Mike and Josh, you are really nice. thank you a lot for your help.

               

              I have tried Mike's approach. it solved the problem. I had achieved what I wanted to achieve. However, I do miss tableau while I was dealing with this kind of grouping. I urge qlik to make it easier.

               

              I am not keen in scripting so I am sorry that I was not able to understand applymap. Sorry Josh but still appreciate your help.

               

              Edward