Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

new Id with peek() function

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.

12 Replies
swuehl
MVP
MVP

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Your condition is wrong change to this

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

Hope it helps

Celambarasan

Not applicable
Author

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

swuehl
MVP
MVP

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

swuehl
MVP
MVP

Rob,

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

Must remember that!

Regards,

Stefan

Not applicable
Author

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!

swuehl
MVP
MVP

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?

pennetzdorfer
Creator III
Creator III

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