2 Replies Latest reply: Feb 25, 2015 8:01 AM by Guilherme Radiske RSS

    Problem generating a sequence number, need help

      Hi guys,

       

      I'm dealing with a apparently easy problem to solve but i've tried so many times that i can't think of nothing anymore. So here's the deal.

       

       

                                                    

      PersonJobSequence
      BobExec100
      BobExec12
      BobExec12
      ChrisDevII0
      TerryAux0
      TerryAuxII
      TiffanyDev0

       

      (nervermind the zeros, but that's my problem).

       

      See that BOB has 2 jobs, but one got 2 registers. I want to generate a sequence that adds(+1) to the new job for the person. Repeating till the next job row updates. It would be like this:

       

       

                                                    

      PersonJobSequence
      BobExec101
      BobExec12 2
      BobExec12 2
      ChrisDevII1
      TerryAux1
      TerryAuxII 2
      TiffanyDev1

       

      I know it's simple but i can't figure it out. Thanks in advance! =)

        • Re: Problem generating a sequence number, need help
          Massimo Grossi

          RESULT

          1.jpg

           

           

          SCRIPT

          a:

          load *, rowno() as id inline [

          Person, Job, Sequence

          Bob, Exec10, 0

          Bob, Exec12,

          Bob, Exec12,

          Chris, DevII, 0

          Terry, Aux ,0

          Terry, AuxII,

          Tiffany ,Dev, 0

          ];

           

          b:

          NoConcatenate

          load

            id, Person, Job,

            if(Person<>Peek(Person),

            1,

            if(Job<>Peek(Job),

            peek(Sequence) +1,

            peek(Sequence))) as Sequence

          Resident a

          order by Person, Job; 

           

          DROP Table a;

            • Re: Problem generating a sequence number, need help

              Thanks, man! While i was waiting for a response, i've tried this

               

              if(previous(Person)<>Person,1,
                       if(previous(Person)=Person and previous(Job)<>Job,numsum(Peek('Sequencia'),1),Peek('Sequencia'))) as Sequencia

               

              But it didn't seem to work... after some search there was some trash in our data, f***ing the sequence.

               

              Both yours and mine solution works now. Thanks for your attention!

               

              It's always good to know that are more than one solution =)