topic Re: Need help creating a duplicate counter. in QlikView

Need help creating a duplicate counter.

Anonymous — Fri, 28 Aug 2015 22:29:38 GMT

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??

Re: Need help creating a duplicate counter.

Oleg_Troyansky — Fri, 28 Aug 2015 22:32:49 GMT

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

Re: Need help creating a duplicate counter.

swuehl — Fri, 28 Aug 2015 22:36:34 GMT

You can also try

LOAD *,

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

LOAD

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

...

FROM ...;

Re: Need help creating a duplicate counter.

Anonymous — Fri, 28 Aug 2015 22:45:17 GMT

Worked Perfectly, Thank you.

Re: Need help creating a duplicate counter.

Oleg_Troyansky — Sat, 29 Aug 2015 17:40:23 GMT

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