Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

RowNo Error? Duplicate values in Concatenate Load after delete rows

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

MyRowNovalue
2a
2y
3b
4c

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

16 Replies
sunny_talwar

What do you expected to see? This:

Capture.PNG

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'

];

aarkay29
Specialist
Specialist

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

MyRowNovalue
2a
2y
3b
4c

maxgro
MVP
MVP

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'

]

;



1.png

Not applicable
Author

RecNo is not right.

I want an unique value for MyRowNo field

ok.png

Not applicable
Author

Join is not right.

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

Somthig like this:

ok.png

Not applicable
Author

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

aarkay29
Specialist
Specialist

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'

]

;

Not applicable
Author

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 [

....


Not applicable
Author

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

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