Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
swatitomar
Creator
Creator

Use Autonumber() to create Rank for Quartile

Hi team ,

i have to create Quartile (1,2,3,4) for that i should rank all the scores in Desc Order and then allocate the top 25% rank 1st Quartile ,Quartile 2 for next will 25% ,Quartile 3 for next will 25% and Quartile 4 for next will 25%.

i'm calculating this on the basis of title , like aggregate the data on title level .

Title: Trainee, Associate, Senior Associate

i tried with Autonumber(Scorces, Title) as Rank order by Scorces Desc;

and Created the range like : min(rank) &'-' & count(Rank)*.25 as Quartile1 ,  same for other also but in case of ties my calculation is not working right..please check autonumber for my result and for correct result check Rank and Quartile .

Need to work in Script.


No ties


All scoresOrder the scoresRankQuartile
34211
44021
52531
62141
72052
91962
101772
131582
151393
1710103
199113
207123
216134
255144
404154
423164

In case of ties

All scoresOrder the scoresRankQuartileAutonumber RankAutonumber Quartile
3421111
4402121
5303131
5214141
7214141
10196252
10177262
13158272
15139382
171010393
191010393
217123103
215134113
305134113
404154123
423164134
17
18

Thanks in Advance,

8 Replies
swuehl
MVP
MVP

As far as I see, the way you want to handle ties, using the same rank for ties and then continuing with the rec number, creating a gap (like missing rank 5, 11, 14 in your sample), Autonumber() is just not the way to go (it will create a consecutive numbering).

Try using a Peek() approach, like

INPUT:

LOAD [All scores],

     [Order the scores],

     Rank,

     Quartile,

     [Autonumber Rank],

     [Autonumber Quartile]

FROM

[https://community.qlik.com/thread/317584]

(html, codepage is 1252, embedded labels, table is @2)

WHERE [All scores];

LOAD [All scores],

  If(Peek('All scores') = [All scores], Peek('NewRank'), Recno()) as NewRank

RESIDENT INPUT

Order by [All scores] desc;

swatitomar
Creator
Creator
Author

hii.

i have 3-4 fields like,

Tile,Scores, ID ...and i want the answer on the basis of Title, so 1st sort the data on Scores to get the correct rank.

i tired the logic share above but didn't get the correct ans. i want to create the quartile with rank so tried with autonumber() and you are right its not giving me correct ans.

PFA

Use Autonumber() to create Rank for Quartile

swuehl
MVP
MVP

Ok, if you need to restart the counter for each title, try something like this:

INPUT:

LOAD [All scores],

     [Order the scores],

     Rank,

     Quartile,

     [Autonumber Rank],

     [Autonumber Quartile],

     Ceil(RAND()*2) as Title

FROM

[https://community.qlik.com/thread/317584]

(html, codepage is 1252, embedded labels, table is @2)

WHERE [All scores];

LOAD [All scores],

Autonumber(Recno(),Title) as RecnoTitle,

  If( Previous(Title) <> Title,1,If( Peek('All scores') = [All scores], Peek('NewRank'),Autonumber(Recno(),Title))) as NewRank

RESIDENT INPUT

Order by Title, [All scores] desc; 

swatitomar
Creator
Creator
Author

Hi ,

instead of Recno() i used [All scores] in Autonumber but then also getting the value - after [All scores] are same as i want

1,2,3,4,4,6,7, 8, 9.

byAutonumber([All scores], Title) i created Rank_TL field

LOAD [All scores],

Autonumber(Recno(),Title) as RecnoTitle,

  If( Previous(Title) <> Title,1,If( Peek('All scores') = [All scores], Peek('NewRank'),Autonumber([All scores],

Title))) as NewRank

RESIDENT INPUT

Order by Title, [All scores] desc;


PFA


swuehl
MVP
MVP

Take care not to use the Autonumber() with same bucket ID (Title) in two different context, counting two different sequences (Recno() vs. All scores.

Besides that, why you've changed to [All scores]?

swatitomar
Creator
Creator
Author

Now I'm using below one only that you have mentioned but not getting the desired result as

if [All scores]=peek([All scores])after that I want next no. like (4,4,6,7)

I have mentioned the desired result above in Rank and quartile field(in case of ties)

and Autonumber rank field, I have created by my logic  "Autonumber(Scorces, Title) as Rank order by Scorces Desc" which is wrong.

LOAD [All scores],

Autonumber(Recno(),Title) as RecnoTitle,

  If( Previous(Title) <> Title,1,If( Peek('All scores') = [All scores], Peek('NewRank'),Autonumber(Recno(),Title))) as NewRank

RESIDENT INPUT

Order by Title, [All scores] desc; 

swatitomar
Creator
Creator
Author

Hey I'm not able to attach the file that why sharing you the script .

Temp:
LOAD * INLINE [
Total_score, title, ID
5, Analyst, 294589
6, Analyst, 410987
10, Analyst, 429951
17, Analyst, 429351
17, Analyst ,452680
58, Analyst, 430477
44, Analyst, 410986
3 ,Analyst, 410996
54, Analyst, 430241
,Analyst, 299072
,Analyst ,392040
,Analyst, 410997
,Analyst ,415241
,Analyst, 438553
,Analyst, 455797
, Analyst, 455798
, Analyst, 456647

5 ,Associate, 281909
5 ,Associate, 351152
5 ,Associate ,438403
6 ,Associate, 283086
6 ,Associate, 343517
6 ,Associate, 351133
6 ,Associate, 351138
6 ,Associate, 443087
16 ,Associate, 417686
10 ,Associate, 306714
11, Associate ,93952
11 ,Associate, 381254
26, Associate, 414242
15, Associate, 272826
15 ,Associate, 303224
15 ,Associate, 306678
31 ,Associate, 351163
9 ,Associate, 308950
45, Associate, 351142
41, Associate, 306733
63 ,Associate, 87416
63, Associate, 201329
21 ,Associate, 294754
21, Associate, 294916
65, Associate, 415981
44, Associate, 414651
40, Associate, 201471
80, Associate, 306749
46, Associate, 185827
94, Associate, 252889
29, Associate, 445589
68, Associate, 444052
108, Associate, 283272
73, Associate ,285928
90, Associate ,306679  ]
  ;
NoConcatenate
Temp1:
load *,
//AutoNumber( Total_score,title) as Rank,
if(Previous(title)<>title,1,if( peek('Total_score')=Total_score,peek('pre1'),AutoNumber( RecNo(),title)))as pre1
Resident Temp  order by title,Total_score desc ; //where not isnull(Total_score)
drop table Temp;

in UI': just take ilst box title and create table box , which contain score, Id , pre1
in case of analyst I m getting 4, 4, 5 ,6 but I want 4, 4, 6, 7 ,8...

in case of Associate , im getting double 1 don't know the reason as score is different and above same  issue is also there

swuehl
MVP
MVP

The line with the Rank calculation is important, because it forces Qlik to calculate the Rec Nr. per title and for every record, even when the else branch of your  pre1 column does not get evaluated.

But as said, the Rank should also be based on Recno(), not the score.

Hence uncomment the Rank and correct the Autonumber() input.

NoConcatenate
Temp1:
load *,
AutoNumber( recno(),title) as Rank,
Recno() as RecID,
if(Previous(title)<>title,1,if( peek('Total_score')=Total_score,peek('pre1'),AutoNumber( RecNo(),title)))as pre1
Resident Temp  order by title,Total_score desc ; //where not isnull(Total_score)

drop table Temp;

Rank.png