Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
Creator II

Autonumber function not working for Rank

Hello,

I got below table A with 3 fields, STATE, YEAR ID and Amount. The values in Amount field are in descending order for each state. I then created a new column, named 'Rank' using autonumber function and the rank always doesn't work when there's the Amount value is exactly the same for the same state (see table A, red highlights).

What I need is results in table B, blue highlights. Any ideas how this can be done in Qlik Sense load script? 

Here's what I tried in the load script;

LOAD STATE, YEAR ID, Amount, AUTONUMBER(Amount, STATE) as Rank
From Table A;

I also tried below syntax but that did not work. 

LOAD STATE, YEAR ID, Amount, AUTONUMBER(Rowno(), Amount & STATE) as Rank
From Table A;

Table A:

STATE Year ID Amount Rank
FL 50851 17,649,099,835 1
FL 14094 15,467,529,704 2
FL 12825 13,371,201,023 3
FL 45745 11,943,362,998 4
FL 93682 11,071,002,583 5
FL 58067 11,065,228,335 6
FL 73256 8,385,169,723 7
FL 75106 8,261,512,874 8
FL 67455 8,119,943,509 9
FL 65507 7,923,879,865 10
FL 88175 7,810,068,564 11
FL 18351 7,614,566,256 12
FL 83468 7,504,081,281 13
FL 93296 7,455,554,106 14
FL 531 7,268,318,136 15
FL 1531 7,268,318,136 15
CO 73256 13,371,201,023 1
CO 75106 11,943,362,998 2
CO 67455 11,071,002,583 3
CO 65507 11,065,228,335 4
CO 88175 8,385,169,723 5
CO 18351 8,261,512,874 6
CO 83468 8,119,943,509 7
CO 93296 7,923,879,865 8
CO 531 7,810,068,564 9
CO 50851 7,614,566,256 10
CO 14094 7,504,081,281 11
CO 12825 7,455,554,106 12
CO 50851 7,268,318,136 13
CO 14094 7,268,316,036 14
CO 12825 7,268,311,136 15

 

Table B:

STATE Year ID Amount Rank
FL 50851 17,649,099,835 1
FL 14094 15,467,529,704 2
FL 12825 13,371,201,023 3
FL 45745 11,943,362,998 4
FL 93682 11,071,002,583 5
FL 58067 11,065,228,335 6
FL 73256 8,385,169,723 7
FL 75106 8,261,512,874 8
FL 67455 8,119,943,509 9
FL 65507 7,923,879,865 10
FL 88175 7,810,068,564 11
FL 18351 7,614,566,256 12
FL 83468 7,504,081,281 13
FL 93296 7,455,554,106 14
FL 531 7,268,318,136 15
FL 1531 7,268,318,136 16
CO 73256 13,371,201,023 1
CO 75106 11,943,362,998 2
CO 67455 11,071,002,583 3
CO 65507 11,065,228,335 4
CO 88175 8,385,169,723 5
CO 18351 8,261,512,874 6
CO 83468 8,119,943,509 7
CO 93296 7,923,879,865 8
CO 531 7,810,068,564 9
CO 50851 7,614,566,256 10
CO 14094 7,504,081,281 11
CO 12825 7,455,554,106 12
CO 50851 7,268,318,136 13
CO 14094 7,268,316,036 14
CO 12825 7,268,311,136 15

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Use RecNo() instead of RowNo() like this and it should work. There is no need to reference the Amount field. You need something unique per State, which RecNo() will be. 

LOAD STATE, YEAR ID, Amount, AUTONUMBER(RecNo(), STATE) as Rank
From Table A;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Use RecNo() instead of RowNo() like this and it should work. There is no need to reference the Amount field. You need something unique per State, which RecNo() will be. 

LOAD STATE, YEAR ID, Amount, AUTONUMBER(RecNo(), STATE) as Rank
From Table A;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Kushal_Chawda

@newqlik2017 if your data is already sorted you could even try below

LOAD
STATE,
"Year ID",
Amount,
if(STATE<>Previous(STATE),1,rangesum(Peek('Rank'),1)) as Rank