topic autoincrement with rowno() problem in QlikView

autoincrement with rowno() problem

Wed, 29 Feb 2012 08:36:43 GMT

hi everybody

is there a way to autoincrement (rowno() function) a column by group like:

I have:

storeid | store | rowno()
------------------------
3 | store3 | 1
3 | store3 | 2
3 | store3 | 3
3 | store3 | 4
7 | store7 | 5
7 | store7 | 6
7 | store7 | 7

I need:

3 | store3 | 4
7 | store7 | 1
7 | store7 | 2
7 | store7 | 3

Thx in advance

Re: autoincrement with rowno() problem

CELAMBARASAN — Wed, 29 Feb 2012 09:12:45 GMT

Hi,

There is no predefined function for this i think.Try this approach.

Table1:

Load

storeid,

store

From DataSource;

Table2:

Load Distinct

storeid

Resident Table1;

FOR i=1 to NoOfRows('Table2')

LET vStoreid=FieldValue('storeid',$(i));

Let vRows = Alt(NoOfRows('Output'),0);

Output:

LOAD

storeid,

store,

RowNo()-$(vRows) as rowno

Resident

Table1 Where storeid=$(vStoreid);

NEXT i;

Drop Table Table2;

Celambarasan

Edited: Editing problem.Check now. Message was edited by: Celambarasan Adhimulam

autoincrement with rowno() problem

Wed, 29 Feb 2012 09:36:49 GMT

Hi Celambarasan,

thx for answering.

But I dont understand your line:
TableTemp Where storeid='$(vStoreid)'; // ---> what is TableTemp and why the 'where' clause?

cheers

PQI

autoincrement with rowno() problem

Wed, 29 Feb 2012 09:46:35 GMT

Hi, try this code

Temp:

LOAD * INLINE [

storeid, store

3, store3

7, store7

];

Tab:

LOAD

storeid,

store,

IF (peek('storeid')<>storeid,1, peek('counter')+1) AS counter

RESIDENT Temp;

drop table Temp;

autoincrement with rowno() problem

Wed, 29 Feb 2012 11:55:41 GMT

Przemek K schrieb:
Hi, try this code
Temp:
LOAD * INLINE [
storeid, store
3, store3
3, store3
3, store3
3, store3
7, store7
7, store7
7, store7
7, store7
];
Tab:
LOAD
storeid,
store,
IF (peek('storeid')<>storeid,1, peek('counter')+1) AS counter
RESIDENT Temp;
drop table Temp;

My Result for now is not what I want:

StoreID | Store | Counter

x_abc | abc | 1

y_abc | abc | 1
z_abc | abc | 1
q_efg | efg | 1

p_efg | efg | 1

r_efg | efg | 1

t_hij | hij | 1
s_hij | hij | 1

But I need:

StoreID | Store | Counter

x_abc | abc | 1

y_abc | abc | 2
z_abc | abc | 3
q_efg | efg | 1

p_efg | efg | 2

r_efg | efg | 1

t_hij | hij | 2
s_hij | hij | 3

Thx for helping!

autoincrement with rowno() problem

Wed, 29 Feb 2012 11:58:41 GMT

Change the code to

Tab:

LOAD

storeid,

store,

IF (peek('store')<>store,1, peek('counter')+1) AS counter

RESIDENT Temp;

Regards

autoincrement with rowno() problem

CELAMBARASAN — Wed, 29 Feb 2012 11:59:44 GMT

Hi,

You can try with this

Tab:

LOAD

storeid,

store,

IF (previous(store)<>store,1, previous(counter)+1) AS counter

RESIDENT Temp;

Celambarasan

autoincrement with rowno() problem

jagan — Wed, 29 Feb 2012 12:21:17 GMT

Hi,

Use this script

Temp:

LOAD * INLINE [

storeid, store

3, store3

7, store7

];

Tab:

LOAD

storeid,

store,

IF (previous(storeid)<>storeid,1, previous(counter) + 1) AS counter

RESIDENT Temp

ORDER BY storeid;

Regards,

Jagan.