Autonumber, adding numbers to Rows etc

[Anonymous]

Hi,

I have the below field containing the below data;

156489

156489

156489

156789

156789

156789

156789

156789

158978

I need the script to generate a number for each order number, but start a new sequence once it reaches a new Order.

Example below:

WHO	Sequence
156489	1
156489	2
156489	3
156789	1
156789	2
156789	3
156789	4
156789	5
158978	1

Please can someone assist?

Thanks in Advance

[sunny_talwar]

May be this:

LOAD WHO,

     AutoNumber(RowNo(), WHO) as Sequence

FROM ....;

[Anil_Babu_Samineni]

May be use this?

LOAD *, AutoNumber(rowno(),FieldName) as Row Inline [

FieldName

156489

156489

156489

156789

156789

156789

156789

156789

158978

];

[sunny_talwar]

Or this:

Table:

LOAD WHO

FROM....

FinalTable:

LOAD WHO,

     If(WHO = Previous(WHO), RangeSum(Peek('Sequence'), 1), 1) as Sequence

Resident Table

Order by WHO;

This option will give better performance for a larger dataset

[ahaahaaha]

Hi Ruan,

Table1:

LOAD*Inline

[WHO

156489

156489

156489

156789

156789

156789

156789

156789

158978

];

Left Join

LOAD*,

If(Previous(WHO) = WHO, Rangesum(1, peek('Sequence')), 1) as Sequence

Resident Table1;

Result

Regards,

Andrey

[Anonymous]

Thanks This worked ! You Champion

[Anil_Babu_Samineni]

Good, You may close this thread by flag Correct answer. Hope Sunny's 2 solutions should work. But, I don't know how your data seems. Any how some what Left Join works for you

Close this thread, Please