Assign row no

[#1]

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

[MK_QSL · Accepted solution]

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

];

[#3]

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

[MK_QSL]

Provide some more sample data lines..

[#5]

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

[MK_QSL]

Without providing data, how can we know about them?

[deepakqlikview_123]

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

[#8]

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

[#9]

anyone have any ideas on this?

[Anonymous]

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

TESTa:

Load * Inline [
A, B
test1, apple
test1, orange
test2, apple
test3, apple
test3, pear
];

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

do

while

vNrRows>0
TESTc:

noconcatenate

Load *

Resident TESTb

where

isnull(

C);

Let

vNrRows =

NoOfRows('TESTc');

drop

table TESTc;

TEST1:

NoConcatenate

load

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;