Autonumber function not working for Rank

newqlik2017 — Wed, 05 Apr 2023 17:46:03 GMT

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

Re: Autonumber function not working for Rank

rwunderlich — Wed, 05 Apr 2023 22:47:11 GMT

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

Re: Autonumber function not working for Rank

Kushal_Chawda — Wed, 05 Apr 2023 23:14:27 GMT

@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

topic Re: Autonumber function not working for Rank in App Development

Autonumber function not working for Rank

Re: Autonumber function not working for Rank

Re: Autonumber function not working for Rank