topic Problem generating a sequence number, need help in QlikView

Problem generating a sequence number, need help

gradiske — Tue, 24 Feb 2015 17:29:16 GMT

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

Re: Problem generating a sequence number, need help

maxgro — Tue, 24 Feb 2015 20:47:34 GMT

RESULT

SCRIPT

load *, rowno() as id inline [

Person, Job, Sequence

Bob, Exec10, 0

Bob, Exec12,

Chris, DevII, 0

Terry, Aux ,0

Terry, AuxII,

Tiffany ,Dev, 0

];

NoConcatenate

load

id, Person, Job,

if(Person<>Peek(Person),

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

gradiske — Wed, 25 Feb 2015 13:01:57 GMT

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 😃