topic Re: RowNo Error? Duplicate values in Concatenate Load after delete rows in QlikView

RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 17:55:28 GMT

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

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

sunny_talwar — Tue, 28 Mar 2017 18:20:03 GMT

What do you expected to see? This:

Try RecNo() instead of RowNo()

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

RecNo() as MyRowNo

,value

Inline [

value

'a'

'b'

'c'

];

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

aarkay29 — Tue, 28 Mar 2017 18:24:12 GMT

or Use Join instead of concatenate

// 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]:

Join([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

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

maxgro — Tue, 28 Mar 2017 18:30:47 GMT

or maybe

// FIRST LOAD

[test]:

load

RowNo() as MyRowNo

,value

Inline [

value

'x'

'y'

'z'

'w'

'j'

]

;

// REMOVE ROWS

inner keep([test])

load

MyRowNo

resident

[test]

where

MyRowNo=2 or MyRowNo=3

;

let v = NoOfRows('test');

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

$(v) + RowNo() as MyRowNo

,value

Inline [

value

'a'

'b'

'c'

'y'

]

;

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 18:40:04 GMT

RecNo is not right.

I want an unique value for MyRowNo field

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 18:41:20 GMT

Join is not right.

I want an unique value for MyRowNo field and all values (I have to use Concatenate)

Somthig like this:

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 18:46:30 GMT

This solution works fine (thank's a lot!), But why RowNo() function works badly?

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

aarkay29 — Tue, 28 Mar 2017 18:51:37 GMT

Modifying Massimo's script slightly for RecNo() to work

// FIRST LOAD

[test]:

load

RowNo() as MyRowNo

,value

Inline [

value

'x'

'y'

'z'

]

;

let v = NoOfRows('test');

// REMOVE ROWS

inner keep([test])

load

MyRowNo

resident

[test]

where

MyRowNo=2

;

// CONCATENATE ROWS

//[test]:

Concatenate([test])

load

$(v)+RecNo() as MyRowNo

,value

Inline [

value

'a'

'b'

'c'

]

;

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 18:54:32 GMT

With your idea

let v = NoOfRows('test');

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

$(v) + RowNo() as MyRowNo

,value

Inline [

....

It's also possible use RecNo() function, adding 1:

let v = NoOfRows('test');

// CONCATENATE ROWS

[test]:

Concatenate([test])

load

$(v) + RecNo() +1 as MyRowNo

,value

Inline [

....

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 18:56:14 GMT

RecNo() function it's possible but adding 1:

$(v) + RecNo() +1 as MyRowNo

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

maxgro — Tue, 28 Mar 2017 19:00:15 GMT

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

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 19:09:26 GMT

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'

]

;

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

sunny_talwar — Tue, 28 Mar 2017 19:13:18 GMT

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

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

maxgro — Tue, 28 Mar 2017 19:24:05 GMT

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'

]

;

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 19:28:01 GMT

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');

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

maxgro — Tue, 28 Mar 2017 19:47:32 GMT

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'

]

;

Re: RowNo Error? Duplicate values in Concatenate Load after delete rows

Tue, 28 Mar 2017 20:09:37 GMT

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