Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

Tags (2)
1 Solution

Accepted Solutions

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

];

15 Replies

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

];

Not applicable

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..

Not applicable

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?

deepaktibhe
Valued Contributor

Re: Assign row no

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

Not applicable

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

Not applicable

Re: Assign row no

anyone have any ideas on this?

hrlinder
Honored Contributor

Re: Assign row no

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;

Community Browser