topic AutoNumber used for Ranking is not working for second ranking in App Development

AutoNumber used for Ranking is not working for second ranking

toddprete — Tue, 03 Dec 2019 16:09:44 GMT

I am trying to rank 2 separate fields in my script. the first field is ranked perfectly, but the 2nd field is not starting at 1 like expected. Its stating where the previous rank left off:

DATA1:
LOAD
    RowNo() AS Record_Key,
    Outstandings,
    Commitment_Amt,
    Age_Bucket,
    Segment,
    Equivalent_Regulatory_Risk_Rating as ERRR
FROM [lib://QLIK_DataMart (dllcorp_pretete)/MISC/US_Portfolio_Quarterly_Detail.QVD] (qvd);

// Past Due Borrowers
MAP_PAST_DUE:
Mapping
LOAD Record_Key, AutoNumber(RowNo(),Segment) AS Rank_PD
RESIDENT DATA1
where Age_Bucket > 1
and Outstandings > 0
order by Segment, Outstandings desc;

// Watchlist
MAP_WATCHLIST:
Mapping
LOAD Record_Key, AutoNumber(RowNo(),Segment) AS Rank_WL
RESIDENT DATA1
where ERRR = 'Watchlist'
and Outstandings > 0
order by Segment, Outstandings desc;

NoConcatenate
Final_Table:
LOAD * WHERE Rank_Past_Due <= 20 or Rank_Watchlist <= 20;
Load *,
ApplyMap('MAP_PAST_DUE',Record_Key,99999) as Rank_Past_Due,
ApplyMap('MAP_WATCHLIST',Record_Key,99999) as Rank_Watchlist,
Resident DATA1;

drop table DATA1;

Re: AutoNumber used for Ranking is not working for second ranking

andoryuu — Tue, 03 Dec 2019 17:47:49 GMT

What happens if you remove "segment" from your autonumber function? The second parameter is the AutoID which will tie values together across autonumbering functions so that may be whats causing your issue.
Function reference: https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterFunctions/autonumber.htm

Re: AutoNumber used for Ranking is not working for second ranking

toddprete — Tue, 03 Dec 2019 19:17:55 GMT

Thanks for the quick reply, but let me provide a few more details, as your suggestion did not work.

The result set has 7 unique Segments, and I am trying to rank the Outstanding amount for all past due customers from 1 to N within each segment. So each segment (7 of them) should have a ranking from 1-n. Additionally, I am trying to rank the watchlist customers by outstanding amount within each segment. So, two sets of rankings, both ranked by outstanding amount, one for past due customers and one ranking for watchlist customers.

The first ranking works with no problem. Then 2nd ranking (using basically the exact logic) does not restart the ranking at 1.