Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Zara
Contributor III
Contributor III

RowNo

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.

1 Solution

Accepted Solutions
Prashant_N
Contributor III
Contributor III

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

 

View solution in original post

5 Replies
Prashant_N
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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
Contributor III
Contributor III
Author

It is working Prashant, thank you very much.

Prashant_N
Contributor III
Contributor III

Happy to help😁