topic Numbering identical transactions in script in QlikView

Numbering identical transactions in script

Thu, 04 Nov 2010 13:53:55 GMT

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

Numbering identical transactions in script

stephencredmond — Thu, 04 Nov 2010 14:16:14 GMT

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

Numbering identical transactions in script

Thu, 04 Nov 2010 14:33:05 GMT

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?

Numbering identical transactions in script

stephencredmond — Thu, 04 Nov 2010 15:17:34 GMT

Sorry, should have noted that.

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

Regards,

Stephen

Numbering identical transactions in script

Thu, 04 Nov 2010 15:41:13 GMT

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

Thanks Stephen.