Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everybody,
I try to create new Id for each store of my table:
I have:
storeid | storename | timestamp
3a | store3 | 01.01.2012
3b | store3 | 02.01.2012
3c | store3 | 03.01.2012
3d | store3 | 04.01.2012
7a | store7 | 01.01.2012
7b | store7 | 02.01.2012
7c | store7 | 03.01.2012
I need:
storeid | storename | timestamp | newId
3a | store3 | 01.01.2012 | 1
3 b| store3 | 02.01.2012 | 2
3 c| store3 | 03.01.2012 | 3
3 d| store3 | 04.01.2012 | 4
7 a| store7 | 01.01.2012 | 1
7 b| store7 | 02.01.2012 | 2
7 c| store7 | 03.01.2012 | 3
I use the peek() function to compare each store, if they not equal then newId+1 else 1.
My Code look like this:
tab1:
Load
storeid,
storename,
...,
...,
...
from mytable;
newId:
Load
storeid,
storename,
If(peek('storename')<>storename, peek('newId')+1, 1 ) as newId
resident tab1;
My result is:
storeid | storename | timestamp | newId
3a | store3 | 01.01.2012 | -
3 b| store3 | 02.01.2012 | -
3 c| store3 | 03.01.2012 | -
3 d| store3 | 04.01.2012 | -
7 a| store7 | 01.01.2012 | -
7 b| store7 | 02.01.2012 | -
7 c| store7 | 03.01.2012 | -
When I just use the peek() function on storename, like peek('storename'), my result is empty... ???
What I do wrong?
Thx in advance.
Forian,
I've also seen the performance penalty when using a significant amount of autonumber buckets (named counters).
You are using some 12539 named counters in your sample (and probably much more in your real data (one for each order no).
I believe QV has an increasingly hard work to organize the autonumbered values, since AFAIR, QV does handle autonumber'ed values differently from other loaded value (it's calculating the autonumber value implicitely from the value position in the attribute vector or something like this).
I also fall back to peek() when I encouter this performance issue, autonumber seems to be fine with a small amount of named counters, though.
All right, thanks for explaining! Good to know ...
You use
peek('newId')+1
in your formula, but this will not work since Peek() returns NULL in the first record. Try
RangeSum(Peek('newId'),1)
instead.
HIC