topic Re: How to sort into specific columns in QlikView

How to sort into specific columns

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to sort into specific columns

devarasu07 — Thu, 07 Sep 2017 02:13:04 GMT

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, 8

elephant, 51

elephant, 11

elephant, 1

elephant, 0.1

pooper, 21

pooper, 51

toilet, 9

];

chart expression as

count( distinct [count of animals])

Re: How to sort into specific columns

rohitraut — Thu, 07 Sep 2017 04:59:17 GMT

Hello Jamal,

Try This,

Re: How to sort into specific columns

AshutoshBhumkar — Thu, 07 Sep 2017 05:15:04 GMT

Hi Rohit,

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

Re: How to sort into specific columns

omkarvamsi — Thu, 07 Sep 2017 07:51:50 GMT

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

Re: How to sort into specific columns

effinty2112 — Thu, 07 Sep 2017 07:56:14 GMT

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, 8

elephant, 51

elephant, 11

elephant, 1

elephant, 0.1

pooper, 21

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

Re: How to sort into specific columns

vinieme12 — Fri, 08 Sep 2017 08:11:21 GMT

Also take a look at the below

Creating Aging Buckets within QlikView