Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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??
You can also try
LOAD *,
AutoNumber( RecNo(), %KEY_LABOR_OPERATION) as Counter;
LOAD
WORKORDER_TYPE & '/' & ... & '/' & SEQUENCE_NO AS %KEY_LABOR_OPERATION,
...
FROM ...;
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
You can also try
LOAD *,
AutoNumber( RecNo(), %KEY_LABOR_OPERATION) as Counter;
LOAD
WORKORDER_TYPE & '/' & ... & '/' & SEQUENCE_NO AS %KEY_LABOR_OPERATION,
...
FROM ...;
Worked Perfectly, Thank you.
This is slick, Stephan! Never thought of using the Autonumber() function this way!