Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Hello Sunny,
it is correct and much quicker. Thank you.
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