12 Replies Latest reply: Sep 12, 2013 1:23 PM by Henric Cronström RSS

    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.

        • new Id with peek() function
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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
                Celambarasan Adhimulam

                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
                  Rob Wunderlich

                  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
                      Stefan Wühl

                      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

                        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
                            Stefan Wühl

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

                              • Re: new Id with peek() function
                                Stefan Wühl

                                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
                              Henric Cronström

                              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