15 Replies Latest reply: Sep 17, 2014 4:00 AM by Manish Kachhia

# Assign row no

Hi there,

If I had a table of data like the below, is it possible to have 3 column generated where by it increments by 1 each time the record in A is found?

So A can occur one or more times but B is unique.

Hope this makes sens.

Thanks

Aidan

A               B               C

test1         apple          1

test1         orange        2

test2         apple          1

test3         apple          1

test3         pear            2

• ###### Re: Assign row no

Load *, AutoNumber(A&B, A) as ID Inline

[

A,               B,               C

test1,         apple,          1

test1,         orange,        2

test2,         apple,         1

test3,         apple,          1

test3,         pear,            2

];

• ###### Re: Assign row no

The above was just a sample, I have hundreds of thousands of records, all numeric so I dont think the above is feasible, thanks for your help though

• ###### Re: Assign row no

Provide some more sample data lines..

• ###### Re: Assign row no

These data lines are completely random, there is thousands of possibilities

• ###### Re: Assign row no

Without providing data, how can we know about them?

• ###### Re: Assign row no

Rowno() works as a output to load statement try that.

• ###### Re: Assign row no

Hi I have tried that already thanks but the folliwng happens to me

A, B, C

test1, apple, 1

test1, orange, 2

test2, apple, 3

test3, apple, 4

test3, pear, 5

• ###### Re: Re: Assign row no

Hi,

just tested with 100000 rows of numerical values.

The solution mrkachhiaimp provided works quite well:

```table1:
Ceil(Rand()*10000) as B
AutoGenerate 100000;

table2:
AutoNumber(B, A) as C
Resident table1
Order by A, B;

DROP Table table1;
```

hope this helps

regards

Marco

• ###### Re: Assign row no

if the B-Value really doesn't matter, then

AutoNumber(RecNo(), A) as C

could be also a solution.

regards

Marco

• ###### Re: Assign row no

LOL !

• ###### Re: Assign row no

Thanks you kindly

• ###### Re: Assign row no

Aidan,

thanks

regards

Marco

• ###### Re: Assign row no

No Marco, It's OK dear, I am just kidding... Doesn't matter...

• ###### Re: Assign row no

anyone have any ideas on this?

• ###### Re: Assign row no

I have tried this and for the short example it works. Maybe you can adjust it to your requirements?

TESTa:

A, B
test1, apple
test1, orange
test2, apple
test3, apple
test3, pear
]
;

TESTb:
A,
B,
if (A<>Previous(A),1,null()) as C
resident TESTa;
Let vNrRows
= 1;

do

while

vNrRows>0
TESTc:

noconcatenate

Resident TESTb

where

isnull(

C);

Let

vNrRows =

NoOfRows('TESTc');

drop

table TESTc;

TEST1:

NoConcatenate

A,

B,

if (

A=

previous(

A),

previous(

C)+1,

C)

as

C

Resident TESTb;

// where isnull(C);

drop

table TESTb;

rename

table TEST1

to TESTb;

loop;

drop

table TESTa;