Numbering identical transactions in script

[#1]

Hi

I'm trying to number all identical financial transactions [1, 2, 3, ...].

The base data looks like:

In the script i want to add a new field numbering each transaction from 1 and up.

First appearance of DK-00001 is numbered 1, second appearance is numbered 2 etc.

The transaction DK-00002 only appears once and is numbered 1.

I've been unable to find a way to count the number of existing rows with the same Transaction. I cant seem to find a way using EXISTS() and COUNT().

Any ideas?

Best regards

Frederik

[stephencredmond · Accepted solution]

Hi Frederik,

I would probably do it with an IF statement comparing the Previous values:

If(Transaction = Previous(Transaction), Peek('Number')+1, 1) As Number

Regards,

Stephen

[#3]

Hi Stephen

Nice solution.It does have the limit that Previous() only looks at the one row before it. If the transactions are not allocated one after another they will not be found.

Can Previous() be exchanged with a function that looks at all rows and selects the most recent one with the same Transaction?

[stephencredmond]

Sorry, should have noted that.

You should sort your data on the way in or this will not work.

Regards,

Stephen

[#5]

Yea thats the only way i could think of. A solution comparing all rows would probably be too slow anyway.

Thanks Stephen.