cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to sort into specific columns

hello,

I have recently moved from Tableau over to Qlik - I was wondering if anyone knew how to sort things into certain categories.

e.g. I have the following

 Product count of animals dog 1 dog 2 dog 3 dog 1 cat 5 cat 1 cat 2 cat 3 mouse 4 mouse 5 mouse 5 mouse 8 elephant 51 elephant 11 elephant 1 elephant 0.1 pooper 21 pooper 51 pooper 51 toilet 9

I want something like this to be done in Qlik.

0<=1          =>1<=2          >2<=3         >3 <=4          <8     >8

Dog              1                2                        1                  0          0        0

Cat               1

Mouse          0

Elephant      1

Pooper         0

Toilet            0

Essentially seeing the count of animals, and sorting them into the appropriate columns. So Dog has been picked up on 4 different occasions - one the first occasion it was counted 1, then 2, then 3 then 1 again. I want something that shows them grouped into specific fields. Thanks

1 Solution

Accepted Solutions
Creator

Hello Jamal,

Try This,

6 Replies
Master II

Hi,

you can try like this.

if([count of animals]<=1,Dual('0<=1',1),

if([count of animals]>1 and [count of animals]<=2,Dual('=>1<=2',2),

if([count of animals]>2 and [count of animals]<=3,Dual('>2<=3',3),

if([count of animals]>3 and [count of animals]<=4,Dual(' >3 <=4',4),

if([count of animals]<=8,Dual('<8',5),Dual('>8',6)))))) as Buckets;

Product, count of animals

dog, 1

dog, 2

dog, 3

dog, 1

cat, 5

cat, 1

cat, 2

cat, 3

mouse, 4

mouse, 5

mouse, 5

mouse, 8

elephant, 51

elephant, 11

elephant, 1

elephant, 0.1

pooper, 21

pooper, 51

pooper, 51

toilet, 9

];

chart expression as

count( distinct [count of animals])

Creator

Hello Jamal,

Try This,

Partner - Specialist

Hi Rohit,

The better way is to do it by the script rather creating buckets on a chart !

Creator

hi jamal

we can attain the solution using class function.

1.take pivot table.

2.take dimensions as product and calculated dimension as class([count of animals],1) and expressions as sum([count of animals]).

3.click on column class([count of animals],1) and drag to the top right side corner to sort into columns.

please see the attachment of sample application.

Regards

Om

Master

Hi,

Try this script:

Data:

Load RecNo() as ID, *  Inline [

Product, count of animals

dog, 1

dog, 2

dog, 3

dog, 1

cat, 5

cat, 1

cat, 2

cat, 3

mouse, 4

mouse, 5

mouse, 5

mouse, 8

elephant, 51

elephant, 11

elephant, 1

elephant, 0.1

pooper, 21

pooper, 51

pooper, 51

toilet, 9

];

This adds a record ID that we will count in our pivot table:

 Product Qty Group <=1 <=2 <=3 <=4 <=8 >8 cat 1 1 1 1 dog 2 1 1 elephant 2 2 mouse 1 3 pooper 3 toilet 1

Dimensions are Product and the calculated dimension Qty Group =

=Aggr(

Pick(

Match(-1,

sum([count of animals])<=1,

sum([count of animals])<=2,

sum([count of animals])<=3,

sum([count of animals])<=4,

sum([count of animals])<=8,

-1),

Dual('<=1',0),

Dual('<=2',1),

Dual('<=3',2),

Dual('<=4',3),

Dual('<=8',8),

Dual('>8',9))

,Product,ID)

Regards

Andrew

Champion III

Also take a look at the below

Creating Aging Buckets within QlikView

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Community Browser