new Id with peek() function

[#1]

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.

[swuehl]

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]

Hi,

     Your condition is wrong change to this

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

Hope it helps

Celambarasan

[#4]

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]

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]

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]

Rob,

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

Must remember that!

Regards,

Stefan

[#8]

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]

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]

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