How to order record first and then add RowNo?

[#1]

Hi Experts,

    I loaded Excel file into QV and added a RowNo column, but I found the RowNo is based upon the original order of the records in Excel. How can I sort the data in Excel first and then add RowNo column, so that RowNo column have the same order as sorted column?

Longmatch

[MayilVahanan]

Hi

I think, use

order by fieldname asc;

Hope that helps

[#3]

Hi Longmatch

You can use the Order By command in the script to sort the loaded data and then use a left join to add a value of RowNo to each line.

An example of the code is:

//1: Load your Excel table

Source:

LOAD Ref,

     Dim1,

     Dim2,

     Dim3

FROM

[..\Desktop\Example Sort.xls]

(biff, embedded labels, table is Sheet1$);

//2: Reload your table using a resident load and sort the data in the order you wish.  In this case I've sorted by Ref in descending order and have qualified the table to distinguish it from the Source table.

Qualify *;

Source_Sorted:

Load *

Resident Source

order by Ref desc;

Unqualify *;

//3: Drop the original Source table to avoid unnecessary duplicate data in memory

Drop table Source;

//4: Join the value of RowNo() to a key field, in this case Ref.  A left join is fine as every row will have a RowNo.

Left join(Source_Sorted)

load

Source_Sorted.Ref,

Rowno() as Source_Sorted.ID

Resident Source_Sorted;

Data_Sorted.ID	Data_Sorted.Ref	Data_Sorted.Dim1	Data_Sorted.Dim2	Data_Sorted.Dim3
1	10	45	30	50
2	9	24	45	76
3	8	25	20	60
4	7	24	40	50
5	6	100	30	50
6	5	45	30	50
7	4	24	45	76
8	3	25	20	60
9	2	24	40	50
10	1	100	30	50

Here you can see that the Excel table is sorted in Descending order by Ref and the addition of the RowNo has not changed the order but has correctly indexed each row in ascending order.

Remember to remove any sort orders present in the sort tab of the properties of your table box/chart as they would override the script sort making it look like the script hadn't worked.

Hope that helps.

Steve

[jeffmartins · Accepted solution]

Hi longmatch,

You need to use a temporary table before of sorting the table and create the rowno field.

See the attached file. Hope it helps you.

Regards

[#5]

Jeffmartins and stevenblowers, thank you for your quick reply. Both of our solutions are working, but jeffmartins's is simpler so I selected his answer as correct. Since only one correct answer can be selected, I had to select stevenblowers' as helpful solution. Thank you again!!!