Skip to main content
cancel
Showing results for 
Search instead 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

  

Productcount of animals
dog1
dog2
dog3
dog1
cat5
cat1
cat2
cat3
mouse4
mouse 5
mouse 5
mouse8
elephant51
elephant11
elephant1
elephant0.1
pooper21
pooper51
pooper51
toilet9

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
rohitraut
Creator
Creator

Hello Jamal,

Try This,

View solution in original post

6 Replies
devarasu07
Master II
Master II

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
Creator
Creator

Hello Jamal,

Try This,

AshutoshBhumkar
Partner - Specialist
Partner - Specialist

Hi Rohit,

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

omkarvamsi
Creator
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

effinty2112
Master
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 111 1 
dog 211  
elephant 2 2
mouse 13 
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
Champion III
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.