RowNo

[Zara]

Hey guys, I'm looking for some support with the creation of a row number by two different dimensions if possible please.

I am working with two fields first is [CustomerID], second is [Distanceinmiles] and I want [distanceinmiles] sorted largest to smallest.

I've attached what i am looking for as an example.

I've currently got AutoNumber(Rec(),CustomerID) as RowNo in my data load and that is giving each CustomerID a row number from 1 - 10 which is great, but how do i organise that row number by [Distanceinmiles] smallest to largest.

Thanks in advance guys, I've been playing around with row no, rec no and auto number for ages and cant figure it out.

[Prashant_N]

Hi,

Take the resident of that same table and write the order by script like below

tbl:

Load *

resident first_table order by CustomerID,RowNo DESC;

Hope it helps

Regards,

Prashant

[Zara]

Hello Prashant, 

Thank you very much for your response, however i am afraid i don't understand.

I am looking to give each row a number based first on the customerid and then second order by distance.

So essentially in real words there are 10 rows with a customerid of 96, of the 10 rows, give each one a number from 1-10 in order of distance smallest to largest. So shouldn't distance be in the script statement you have written for me?

Thank you so much - apologies if i am asking a stupid question.

[Prashant_N · Accepted solution]

Yes, Thats my bad😅.

You can do this with below script

Load [CustomerID],

[Distanceinmiles] ,

 AutoNumber(RecNo(),CustomerID) as RowNo

resident table order by CustomerID,Distanceinmiles ASC/DESC;

Hope it helps.

Regards,

Prashant

[Zara]

It is working Prashant, thank you very much.

[Prashant_N]

Happy to help😁