topic new Id with peek() function in QlikView

new Id with peek() function

Tue, 06 Mar 2012 14:25:24 GMT

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.

new Id with peek() function

swuehl — Tue, 06 Mar 2012 14:36:16 GMT

Hi,

what version are you using?

This seems to work quite fine in QV11:

tab1:

LOAD * INLINE [

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

] (delimiter is '|');

newId:

Load

storeid,

storename,

If(peek('storename')=storename, peek('newId')+1, 1 ) as newId

resident tab1;

drop table tab1;

Note that I changed <> to equal sign = (I think that's what you logically need, but this won't explain the problem you have).

Stefan

new Id with peek() function

CELAMBARASAN — Tue, 06 Mar 2012 14:42:19 GMT

Hi,

Your condition is wrong change to this

If(peek('storename')<>storename,1, peek('newId')+1 ) as newId

Hope it helps

Celambarasan

new Id with peek() function

Tue, 06 Mar 2012 14:50:58 GMT

swuehl schrieb:
Hi,
what version are you using?
This seems to work quite fine in QV11:
tab1:
LOAD * INLINE [
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
] (delimiter is '|');
newId:
Load
storeid,
storename,
If(peek('storename')=storename, peek('newId')+1, 1 ) as newId
resident tab1;
drop table tab1;
Note that I changed <> to equal sign = (I think that's what you logically need, but this won't explain the problem you have).
Stefan

I'm using QV 10 and when I do something like this:

Temp:

LOAD * INLINE [

storeid, store

3a, store3

3s, store3

3d, store3

3f, store3

7f, store7

7h, store7

7m, store7

7u, store7

];

Tab:

LOAD

storeid,

store,

IF (peek('store')=store,peek('counter')+1,1 ) AS counter,

peek('store') as Peek,

RESIDENT Temp;

It's fine too.

But it doesn't work on my original table!!!

new Id with peek() function

swuehl — Tue, 06 Mar 2012 15:01:26 GMT

Not sure what's going on, seems that your original table differs from the example you posted above.

Besides that, I would recommend using a sort with the resident load you create the newId,

LOAD

...

resident Tab1 order by storename, timestamp;

use the fields with appropriate sort order as needed to create your correct newID sequence order.

Again, this won't explain why even peek('storename') doesn't return anything, so you need to recheck your original table and check that field names are correctly spelled etc.

new Id with peek() function

rwunderlich — Tue, 06 Mar 2012 18:51:46 GMT

Without commenting on why your peek() is not working, I'd like to suggest that an easier way to generate the Ids is with AutoNumber()

AutoNumber(RecNo(), storename) as NewId

-Rob

http://robwunderlich.com

new Id with peek() function

swuehl — Tue, 06 Mar 2012 19:51:51 GMT

Rob,

that's a very neat use of the second parameter 'AutoID' to autonumber(), haven't seen that before.

Must remember that!

Regards,

Stefan

new Id with peek() function

Wed, 07 Mar 2012 14:46:40 GMT

Hi Rob,

thx for advice but its only bring me one step closer.

My original table:

storeid | storename | date | orderEtc

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

3 g| store3 | 04.01.2012 | 5
7 a| store7 | 01.01.2012 | 2
7 b| store7 | 02.01.2012 | 3

7 c| store7 | 03.01.2012 | 4

My result now is:

storeid | store | orderEtc | newId
3a | store3 | 01.01.2012 | 1 | 1
3 b| store3 | 02.01.2012 | 2 |1
3 c| store3 | 03.01.2012 | 3 | 1
3 e| store3 | 04.01.2012 | 4| 1

3 f| store3 | 05.01.2012 | 5| 1
3a | store3 | 01.01.2012 | 1 | 2
3 b| store3 | 02.01.2012 | 2 | 2
3 c| store3 | 03.01.2012 | 3 | 2
3 e| store3 | 04.01.2012 | 4| 2

3 f| store3 | 05.01.2012 | 5| 2

3a | store3 | 01.01.2012 | 1 | 3
3 b| store3 | 02.01.2012 | 2 | 3
3 c| store3 | 03.01.2012 | 3 | 3
3 e| store3 | 04.01.2012 | 4| 3

3 f| store3 | 05.01.2012 | 5| 3

3a | store3 | 01.01.2012 | 1 | 4
3 b| store3 | 02.01.2012 | 2 | 4
3 c| store3 | 03.01.2012 | 3 | 4
3 e| store3 | 04.01.2012 | 4| 4

3 f| store3 | 05.01.2012 | 5| 4

3a | store3 | 01.01.2012 | 1 | 5
3 b| store3 | 02.01.2012 | 2 | 5
3 c| store3 | 03.01.2012 | 3 | 5
3 e| store3 | 04.01.2012 | 4| 5

3 f| store3 | 05.01.2012 | 5| 5

It looks like I get for every OrderNumber a new ID

But I need a new ID for every store!

I want:

storeid | storename | date | orderEtc | newId

3a | store3 | 01.01.2012 | 1 | 1
3 b| store3 | 02.01.2012 | 2 | 2
3 c| store3 | 03.01.2012 | 3 | 3
3 d| store3 | 04.01.2012 | 4 | 4

3 g| store3 | 04.01.2012 | 5 | 5

7 a| store7 | 01.01.2012 | 1 | 1
7 b| store7 | 02.01.2012 | 2 | 2

7 c| store7 | 03.01.2012 | 3 | 3

THX!

new Id with peek() function

swuehl — Wed, 07 Mar 2012 15:28:13 GMT

I do get your requested table output when using:

tab:

LOAD * INLINE [

storeid | storename | date | orderEtc

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

3 g| store3 | 04.01.2012 | 5

7 a| store7 | 01.01.2012 | 2

7 b| store7 | 02.01.2012 | 3

7 c| store7 | 03.01.2012 | 4

] (delimiter is '|');

Tab2:

LOAD

storeid,

storename,

date,

orderEtc,

AutoNumber(recno(), storename) as newID

Resident tab;

drop table tab;

Not sure why you get this bloated table, seems like an outer join to me, could you post your script or at best a small sample file?

Re: new Id with peek() function

pennetzdorfer — Thu, 12 Sep 2013 10:44:03 GMT

Rob,

I like the AutoNumber method, but encountered a performance issue: When I use AutoNumber() instead of Peek() the script execution time increases significantly. Would you mind checking the attached qvw? (= small extract of my data ... the original qvd contains about 10 Million records, that's why the difference really matters).

Of course, I could stick to the Peek() function ... but: I need to create an additional ID that would require another order by clause. Using Peek(), I would have to do an extra resident load. With AutoNumber() I could do that at once.

Regards,

Florian

Re: new Id with peek() function

swuehl — Thu, 12 Sep 2013 11:42:28 GMT

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.

Re: new Id with peek() function

pennetzdorfer — Thu, 12 Sep 2013 12:54:47 GMT

All right, thanks for explaining! Good to know ...

Re: new Id with peek() function

hic — Thu, 12 Sep 2013 17:23:36 GMT

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