topic Re: Assign row no in QlikView

Assign row no

Tue, 16 Sep 2014 08:49:42 GMT

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

MK_QSL — Tue, 16 Sep 2014 08:52:16 GMT

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

Tue, 16 Sep 2014 08:58:21 GMT

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

MK_QSL — Tue, 16 Sep 2014 09:04:54 GMT

Provide some more sample data lines..

Re: Assign row no

Tue, 16 Sep 2014 09:06:47 GMT

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

Re: Assign row no

MK_QSL — Tue, 16 Sep 2014 09:09:06 GMT

Without providing data, how can we know about them?

Re: Assign row no

deepakqlikview_123 — Tue, 16 Sep 2014 09:15:38 GMT

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

Re: Assign row no

Tue, 16 Sep 2014 09:24:19 GMT

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: Assign row no

Tue, 16 Sep 2014 12:11:43 GMT

anyone have any ideas on this?

Re: Assign row no

Anonymous — Tue, 16 Sep 2014 13:25:05 GMT

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;

while

vNrRows>0
TESTc:

noconcatenate

Load *

Resident TESTb

where

isnull(

C);

Let

vNrRows =

NoOfRows('TESTc');

drop

table TESTc;

TEST1:

NoConcatenate

load

if (

previous(

A),

previous(

C)+1,

Resident TESTb;

// where isnull(C);

drop

table TESTb;

rename

table TEST1

to TESTb;

loop;

drop

table TESTa;

Re: Re: Assign row no

MarcoWedel — Tue, 16 Sep 2014 23:26:06 GMT

Hi,

just tested with 100000 rows of numerical values.

The solution Manish Kachhia provided works quite well:

table1:
LOAD Ceil(Rand()*10000) as A,
    Ceil(Rand()*10000) as B
AutoGenerate 100000;

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

DROP Table table1;

hope this helps

regards

Marco

Re: Assign row no

MarcoWedel — Tue, 16 Sep 2014 23:30:59 GMT

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

Wed, 17 Sep 2014 07:16:08 GMT

Thanks you kindly

Re: Assign row no

MK_QSL — Wed, 17 Sep 2014 07:47:15 GMT

LOL !

Still my answer is not even correct or helpful ! 🙂

Re: Assign row no

MarcoWedel — Wed, 17 Sep 2014 07:57:38 GMT

Aidan,

please mark Manish Kachhia's answer as correct instead of mine. I just tested it for you.

thanks

regards

Marco

Re: Assign row no

MK_QSL — Wed, 17 Sep 2014 07:59:45 GMT

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