Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

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

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.

DateSupplierItem_idRank
05/09/2020469071021038
20/10/2020469071021037
12/02/2021469071021036
17/02/2021469071021035
01/03/2021469071021034
09/03/2021469071021033
15/03/2021469071021032
06/04/2021469071021031
05/09/2020469071100367
20/10/2020469071100366
09/03/2021469071100365
09/07/2021469071100364
13/07/2021469071100363
14/07/2021469071100362
27/08/2021469071100361

 

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

Thank you in advance.

 

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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;

View solution in original post

geogou1973
Creator
Creator
Author

Hello Sunny,

it is correct and much quicker. Thank you.