Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
gradiske
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

2 Replies
maxgro
MVP
MVP

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;

gradiske
Contributor III
Contributor III
Author

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 😃