Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!!
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
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'
]
;
May be use AutoNumber(value) if value won't repeat or AutoNumber(value&RowNo())
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'
]
;
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');
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'
]
;
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