Problem generating a sequence number, need help

[gradiske]

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.

Person	Job	Sequence
Bob	Exec10	0
Bob	Exec12
Bob	Exec12
Chris	DevII	0
Terry	Aux	0
Terry	AuxII
Tiffany	Dev	0

(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:

Person	Job	Sequence
Bob	Exec10	1
Bob	Exec12	2
Bob	Exec12	2
Chris	DevII	1
Terry	Aux	1
Terry	AuxII	2
Tiffany	Dev	1

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

[maxgro · Accepted solution]

RESULT

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;

[gradiske]

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 😃