topic Use Autonumber() to create Rank for Quartile in QlikView

Use Autonumber() to create Rank for Quartile

swatitomar — Mon, 22 Oct 2018 15:05:37 GMT

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 scores	Order the scores	Rank	Quartile
3	42	1	1
4	40	2	1
5	25	3	1
6	21	4	1
7	20	5	2
9	19	6	2
10	17	7	2
13	15	8	2
15	13	9	3
17	10	10	3
19	9	11	3
20	7	12	3
21	6	13	4
25	5	14	4
40	4	15	4
42	3	16	4

In case of ties

All scores	Order the scores	Rank	Quartile	*Autonumber Rank*	*Autonumber Quartile*
3	42	1	1	1	1
4	40	2	1	2	1
5	30	3	1	3	1
5	21	4	1	4	1
7	21	4	1	4	1
10	19	6	2	5	2
10	17	7	2	6	2
13	15	8	2	7	2
15	13	9	3	8	2
17	10	10	3	9	3
19	10	10	3	9	3
21	7	12	3	10	3
21	5	13	4	11	3
30	5	13	4	11	3
40	4	15	4	12	3
42	3	16	4	13	4
		17
		18

Thanks in Advance,

Re: Use Autonumber() to create Rank for Quartile

swuehl — Mon, 22 Oct 2018 15:38:48 GMT

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;

Re: Use Autonumber() to create Rank for Quartile

swatitomar — Mon, 22 Oct 2018 16:20:01 GMT

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

Re: Use Autonumber() to create Rank for Quartile

swuehl — Mon, 22 Oct 2018 16:35:39 GMT

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;

Re: Use Autonumber() to create Rank for Quartile

swatitomar — Mon, 22 Oct 2018 17:09:23 GMT

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

Re: Use Autonumber() to create Rank for Quartile

swuehl — Mon, 22 Oct 2018 17:20:38 GMT

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]?

Re: Use Autonumber() to create Rank for Quartile

swatitomar — Tue, 23 Oct 2018 03:03:16 GMT

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;

Re: Use Autonumber() to create Rank for Quartile

swatitomar — Tue, 23 Oct 2018 03:29:38 GMT

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

Re: Use Autonumber() to create Rank for Quartile

swuehl — Tue, 23 Oct 2018 08:27:45 GMT

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;

All scores	Order the scores	Rank	Quartile
3	42	1	1
4	40	2	1
5	25	3	1
6	21	4	1
7	20	5	2
9	19	6	2
10	17	7	2
13	15	8	2
15	13	9	3
17	10	10	3
19	9	11	3
20	7	12	3
21	6	13	4
25	5	14	4
40	4	15	4
42	3	16	4

All scores	Order the scores	Rank	Quartile	*Autonumber Rank*	*Autonumber Quartile*
3	42	1	1	1	1
4	40	2	1	2	1
5	30	3	1	3	1
5	21	4	1	4	1
7	21	4	1	4	1
10	19	6	2	5	2
10	17	7	2	6	2
13	15	8	2	7	2
15	13	9	3	8	2
17	10	10	3	9	3
19	10	10	3	9	3
21	7	12	3	10	3
21	5	13	4	11	3
30	5	13	4	11	3
40	4	15	4	12	3
42	3	16	4	13	4
		17
		18

All scores	Order the scores	Rank	Quartile
3	42	1	1
4	40	2	1
5	25	3	1
6	21	4	1
7	20	5	2
9	19	6	2
10	17	7	2
13	15	8	2
15	13	9	3
17	10	10	3
19	9	11	3
20	7	12	3
21	6	13	4
25	5	14	4
40	4	15	4
42	3	16	4

All scores	Order the scores	Rank	Quartile	*Autonumber Rank*	*Autonumber Quartile*
3	42	1	1	1	1
4	40	2	1	2	1
5	30	3	1	3	1
5	21	4	1	4	1
7	21	4	1	4	1
10	19	6	2	5	2
10	17	7	2	6	2
13	15	8	2	7	2
15	13	9	3	8	2
17	10	10	3	9	3
19	10	10	3	9	3
21	7	12	3	10	3
21	5	13	4	11	3
30	5	13	4	11	3
40	4	15	4	12	3
42	3	16	4	13	4
		17
		18

All scores	Order the scores	Rank	Quartile
3	42	1	1
4	40	2	1
5	25	3	1
6	21	4	1
7	20	5	2
9	19	6	2
10	17	7	2
13	15	8	2
15	13	9	3
17	10	10	3
19	9	11	3
20	7	12	3
21	6	13	4
25	5	14	4
40	4	15	4
42	3	16	4

All scores	Order the scores	Rank	Quartile	*Autonumber Rank*	*Autonumber Quartile*
3	42	1	1	1	1
4	40	2	1	2	1
5	30	3	1	3	1
5	21	4	1	4	1
7	21	4	1	4	1
10	19	6	2	5	2
10	17	7	2	6	2
13	15	8	2	7	2
15	13	9	3	8	2
17	10	10	3	9	3
19	10	10	3	9	3
21	7	12	3	10	3
21	5	13	4	11	3
30	5	13	4	11	3
40	4	15	4	12	3
42	3	16	4	13	4
		17
		18