Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Mikiyo
Contributor
Contributor

If there are duplicate values, how to display the top three

Hi,i am new to community,there is a questin whitch bother me for sevaral days,can anyone help me ? thanks in advance.

now ,i have some data like this:

code    type    qty

DG0219   A      2

DG0219  B      30

DG0219  C    1

DG0219   D   2

DG0219   E   1

DG0219   F   1

DG0219   G   1

DG0219    H   1

what i want to display is like this:

code      NO.1 type     NO.2 type     NO.3 type

DG0219       B                    A;D               C;E;F;G;H

I tried these:

=aggr(contact(if(aggr(rank(Qty,1),code,type)=1,type),';'),code)

=aggr(contact(if(aggr(rank(Qty,1),code,type)=2,type),';'),code)

=aggr(contact(if(aggr(rank(Qty,1),code,type)=3,type),';'),code)

to show NO.1,NO.2,NO.3,but what i can got just like this:

code      NO.1 type     NO.2 type     NO.3 type

DG0219       B                    A;D               C;E;F;G;H

i guess maybe there was tied for third place so it can't  show all of them,so how can i get the proper rank?

2 Solutions

Accepted Solutions
PrashantSangle

I think a simple max() will work for you

 

try below 3 expression

1: No1 Type :  Concat({<qty={"$(=max(qty,1))"}>}type,';')

2: No2 Type : Concat({<qty={"$(=max(qty,2))"}>}type,';')

3: No3 Type :Concat({<qty={"$(=max(qty,3))"}>}type,';')

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

rubenmarin

Hi, this is beacuse 2nd and 3rd are tied,so from rank 2 it jumps to rank 4, something similar might happen if there are more than one in first position.

For this data this might work:

=concat(aggr(if(qty=Max(Total <code> qty),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,2),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,3),type),code,type),';')

View solution in original post

3 Replies
PrashantSangle

I think a simple max() will work for you

 

try below 3 expression

1: No1 Type :  Concat({<qty={"$(=max(qty,1))"}>}type,';')

2: No2 Type : Concat({<qty={"$(=max(qty,2))"}>}type,';')

3: No3 Type :Concat({<qty={"$(=max(qty,3))"}>}type,';')

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
rubenmarin

Hi, this is beacuse 2nd and 3rd are tied,so from rank 2 it jumps to rank 4, something similar might happen if there are more than one in first position.

For this data this might work:

=concat(aggr(if(qty=Max(Total <code> qty),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,2),type),code,type),';')

=concat(aggr(if(qty=Max(Total <code> qty,3),type),code,type),';')

Mikiyo
Contributor
Contributor
Author

That works great,thank u,Rubenmarin,This makes me have a deeper understanding of rank function and Max function