Create a rowno() depending of fields and sorting according the fields

[geogou1973]

Hello, 

I am facing the following problem. I want to create a row number sorted and depending of some fields.

I have the following script which the field KEY is the concatenation of NFOUNA&NARTNA 

TMP:
LOAD DDVANA_NEW as Date,
KEY,
NARTNA as Item_id,
NFOUNA as Supplier,
FROM [F:\SUPPLIERS.QVD] (qvd)

where NFOUNA = 46907;

NoConcatenate

TABLE:
LOAD *,AutoNumber(RowNo(),KEY) as Rank
Resident TMP
Order By Date desc,Supplier,Item_id;

DROP TABLE TMP;

the results i take are correct and are the following but if i run the whole qvd that contains over 2000000 rows the first read of the script ends in seconds but the other read of the autonumber is too slow and it take over of three hours.

Date	Supplier	Item_id	Rank
05/09/2020	46907	102103	8
20/10/2020	46907	102103	7
12/02/2021	46907	102103	6
17/02/2021	46907	102103	5
01/03/2021	46907	102103	4
09/03/2021	46907	102103	3
15/03/2021	46907	102103	2
06/04/2021	46907	102103	1
05/09/2020	46907	110036	7
20/10/2020	46907	110036	6
09/03/2021	46907	110036	5
09/07/2021	46907	110036	4
13/07/2021	46907	110036	3
14/07/2021	46907	110036	2
27/08/2021	46907	110036	1

Has anyone an idea how i can replace the script in order to have the same results in sorter time ?

Thank you in advance.

[sunny_talwar · Accepted solution]

AutoNumber can be slow, try using Peek function instead

TMP:
LOAD DDVANA_NEW as Date,
     KEY,
     NARTNA as Item_id,
     NFOUNA as Supplier,
FROM [F:\SUPPLIERS.QVD] (qvd)
Where NFOUNA = 46907;

TABLE:
NoConcatenate
LOAD *,
     If(Supplier = Previous(Supplier),
         If(Item_id = Previous(Item_id),
             RangeSum(Peek('Rank'), 1),
             1
          ),
          1
     ) as Rank
Resident TMP
Order By Supplier, Item_id, Date desc;

[geogou1973]

Hello Sunny,

it is correct and much quicker. Thank you.

[erric3210]

Hi @sunny_talwar That's a good solution. But how to Order By Supplier if  it has a Text value rather than numeric value.

Regards,

Eric