Need help creating a duplicate counter.

[Anonymous]

So I have a few fields combined to make a Key field.

WORKORDER_TYPE & '/' & ...  & '/' & SEQUENCE_NO AS %KEY_LABOR_OPERATION

I want something that counts the duplicates as 1, 2, 3...

%KEY_LABOR_OPERATION	Counter
Released/88	1
Released/88	2
Released/88	3
Released/89	1
Released/89	2
Released/90	1
Released/90	2
Released/90	3
Released/90	4
Released/91	1

Is this possible??

[Oleg_Troyansky]

Yes, of course - this is a common algorithm of calculating running balances. In order to do that, you need to reload your data, sorted by your key fields, and use functions peek() and previous() to compare the keys with the previous row and to calculate the counter.

I use and explain this algorithm in a lot of details in my book - it's used to calculate the Age of inventory (which is not a very trivial exercise...)

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[swuehl · Accepted solution]

You can also try

LOAD *,

          AutoNumber( RecNo(), %KEY_LABOR_OPERATION) as Counter;

LOAD

     WORKORDER_TYPE & '/' & ...  & '/' & SEQUENCE_NO AS %KEY_LABOR_OPERATION,

     ...

FROM ...;

[Anonymous]

Worked Perfectly, Thank you.

[Oleg_Troyansky]

This is slick, Stephan! Never thought of using the Autonumber() function this way!