Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jacobesalas
New Contributor II

Need help creating a duplicate counter.

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_OPERATIONCounter
Released/881
Released/882
Released/883
Released/891
Released/892
Released/901
Released/902
Released/903
Released/904
Released/911

Is this possible??

1 Solution

Accepted Solutions
MVP
MVP

Re: Need help creating a duplicate counter.

You can also try

LOAD *,

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

LOAD

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

     ...

FROM ...;

4 Replies
MVP
MVP

Re: Need help creating a duplicate counter.

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

MVP
MVP

Re: Need help creating a duplicate counter.

You can also try

LOAD *,

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

LOAD

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

     ...

FROM ...;

jacobesalas
New Contributor II

Re: Need help creating a duplicate counter.

Worked Perfectly, Thank you.

MVP
MVP

Re: Need help creating a duplicate counter.

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

Community Browser