Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
Hello Jamal,
Try This,
Hi Rohit,
The better way is to do it by the script rather creating buckets on a chart !
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
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
Also take a look at the below
Creating Aging Buckets within QlikView