How to reset counter at each unique value in script?

[devan9876]

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

[Anil_Babu_Samineni]

May be this?

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

FieldName

Dog

Dog

Dog

Dog

Cat

Cat

Chicken

Chicken

Bird

];

[sunny_talwar · Accepted solution]

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;

[devan9876]

That seems to work.

Thanks!