How to sort into specific columns

[Anonymous]

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

[devarasu07]

Hi,

you can try like this.

load *,

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;

LOAD * 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

];

chart expression as

count( distinct [count of animals])

[rohitraut · Accepted solution]

Hello Jamal,

Try This,

[AshutoshBhumkar]

Hi Rohit,

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

[omkarvamsi]

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

[effinty2112]

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

[vinieme12]

Also take a look at the below

Creating Aging Buckets within QlikView