How to group to a new dimension?

[#1]

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

[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

[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.

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

HTH

Mike T

Qlik

[Josh_Good]

An alternated solution would be to apply a map.  This post explains how to do this.

Apply Map Concept

-Josh

[#5]

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

[Josh_Good]

Edward,

Glad to hear you got it figured out. I'll pass your feedback to our products team.

-Josh