RowNo Error? Duplicate values in Concatenate Load after delete rows

[#1]

RowNo() inserts duplicates values after I've delete rows.

This is a very simple example:

// FIRST LOAD

[test]:

load

  RowNo() as MyRowNo

  ,value

Inline [

  value

  'x'

  'y'

  'z'

];

// REMOVE ROWS

inner keep([test])

load

  MyRowNo

resident

  [test]

where

  MyRowNo=2

;

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

  RowNo() as MyRowNo

  ,value

Inline [

  value

  'a'

  'b'

  'c'

];

And the result is

MyRowNo	value
2	a
2	y
3	b
4	c

Why I've duplicate values? How to resolve? This is a simple example but my code is very complex !!!

[maxgro]

a     1

b     2

c     3

remove a with keep

you have 2 rows

b     2

c     3

so next rowno() will return 3 = num of rows of the table + 1

d     3

e     4

[#3]

I'm sorry, but after som test, your solution doesn't works

This my modified script:

// FIRST LOAD (more rows)

[test]:

load

  RowNo() as MyRowNo

  ,value

Inline [

  value

  'j'

  'x'

  'y'

  'w'

  'z'

]

;

// REMOVE ROWS

inner keep([test])

load

  MyRowNo

resident

  [test]

where

  MyRowNo=4 or MyRowNo=5 // delete last rows

;

let v = NoOfRows('test');

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

  RowNo()+$(v) as MyRowNo

  ,value

Inline [

  value

  'a'

  'b'

  'c'

  'd'

  'e'

]

;

[sunny_talwar]

May be use AutoNumber(value) if value won't repeat or AutoNumber(value&RowNo())

[maxgro]

maybe

// FIRST LOAD (more rows)

[test]:

load

  RowNo() as MyRowNo

  ,value

Inline [

  value

  'j'

  'x'

  'y'

  'w'

  'z'

]

;

// REMOVE ROWS

inner keep([test])

load

  MyRowNo

resident

  [test]

where

  MyRowNo=4 or MyRowNo=5 // delete last rows

;

tmp: load max(MyRowNo) as Max Resident test;

let v = Peek('Max') - NoOfRows('test');

DROP Table tmp;

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

  RowNo()+$(v) as MyRowNo

  ,value

Inline [

  value

  'a'

  'b'

  'c'

  'd'

  'e'

]

;

// REMOVE ROWS

inner keep([test])

load

  MyRowNo

resident

  [test]

where

  value = 'd' or value = 'e' // delete last rows

;

tmp: load max(MyRowNo) as Max Resident test;

let v = Peek('Max') - NoOfRows('test');

DROP Table tmp;

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

  RowNo()+$(v) as MyRowNo

  ,value

Inline [

  value

  'aa'

  'bb'

]

;

[#6]

No: this is a very simple example, I've got a lot of fields (and a lot of records!!)

Instead of

let v = NoOfRows('test');

I try this and works fine, but is too much slow.....

TempMaxExpNr:

Load Max(MyRowNo) as MaxMyRowNo resident test;

Let v = Peek('MaxMyRowNo');

[maxgro]

if the resident load is too slow,

replace with the bold

// FIRST LOAD (more rows)

[test]:

load

  RowNo() as MyRowNo

  ,value

Inline [

  value

  'j'

  'x'

  'y'

  'w'

  'z'

]

;

// REMOVE ROWS

t: inner keep(test)

load

  MyRowNo

resident

  [test]

where

  value='w' or value='z' // delete last rows

;

//tmp: load max(MyRowNo) as Max Resident test;

tmp: LOAD max(FieldValue('MyRowNo', recno())) as Max

AUTOGENERATE FieldValueCount('MyRowNo');

let v = Peek('Max') - NoOfRows('test');

DROP Table tmp;

// CONCATENATE ROWS

Concatenate (test)

load

  RowNo()+$(v) as MyRowNo

  ,value

Inline [

  value

  'a'

  'b'

  'c'

  'd'

  'e'

]

;

// REMOVE ROWS

inner keep([test])

load

  MyRowNo

resident

  [test]

where

  value = 'd' or value = 'e' or value = 'w' or value = 'z' // delete last rows

;

//tmp: load max(MyRowNo) as Max Resident test;

tmp: LOAD max(FieldValue('MyRowNo', recno())) as Max

AUTOGENERATE FieldValueCount('MyRowNo');

let v = Peek('Max') - NoOfRows('test');

DROP Table tmp;

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

  RowNo()+$(v) as MyRowNo

  ,value

Inline [

  value

  'aa'

  'bb'

]

;

[#8 · Accepted solution]

This is the best way (from https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/😞

tmp:

LOAD Max(Id) as MaxId;

LOAD FieldValue('MyRowNo', recno()) as Id

AUTOGENERATE FieldValueCount('MyRowNo');

let v = Peek('MaxId');

trace $(v);

DROP Table tmp;

I also try this sintax, but it doesn't work (it's without use preloading):

tmp:

LOAD Max(FieldValue('MyRowNo', recno())) as MaxId

AUTOGENERATE FieldValueCount('MyRowNo'); 

let v = Peek('MaxId');

trace $(v);

DROP Table tmp;

Thanks to everyone (specially to Massimo Grossi) for their help and ideas