Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to reset counter at each unique value in script?

Hypothetical Source Data:

Dog

Dog

Dog

Dog

Cat

Cat

Chicken

Chicken

Bird

Desired Output:

Dog|1

Dog|2

Dog|3

Dog|4

Cat|1

Cat|2

Cat|3

Chicken|1

Chicken|2

Bird|1

1 Solution

Accepted Solutions
Highlighted

2 Ways

1) Using Peek/Previous

Fact:

LOAD Field

FROM ...;

FinalFact:

LOAD Field,

     If(Field = Previous(Field), RangeSum(Peek('Counter'), 1), 1) as Counter

Resident Fact

Order By Field;

2) Using AutoNumber

Fact:

LOAD Field

FROM ...;

FinalFact:

LOAD Field,

     AutoNumber(RecNo(), Field) as Counter

Resident Fact

Order By Field;

View solution in original post

3 Replies
Highlighted

May be this?

LOAD FieldName & '|' & AutoNumber(RecNo(), FieldName) as FieldName Inline [

FieldName

Dog

Dog

Dog

Dog

Cat

Cat

Chicken

Chicken

Bird

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted

2 Ways

1) Using Peek/Previous

Fact:

LOAD Field

FROM ...;

FinalFact:

LOAD Field,

     If(Field = Previous(Field), RangeSum(Peek('Counter'), 1), 1) as Counter

Resident Fact

Order By Field;

2) Using AutoNumber

Fact:

LOAD Field

FROM ...;

FinalFact:

LOAD Field,

     AutoNumber(RecNo(), Field) as Counter

Resident Fact

Order By Field;

View solution in original post

Highlighted
Contributor III
Contributor III

That seems to work.

Thanks!