Running count of occurrences in load script

[Anonymous]

I wonder if anyone can point me in the right direction here.  I have a table like this:

Category      Value

category 1     .1

category 2     .3

category 2     .2

category 1     .1

category 2     .3

category 1     .1

category 2     .2

What I want is a running total of the occurrence number of Category x Value, i.e

Category      Value     Occurrence

category 1     .1          1

category 2     .3          1

category 2     .2          1

category 1     .1          2

category 2     .3          2

category 1     .1          3

category 2     .2          2

I have tried FieldValueCount, but it seems only to work on the whole table, also MixMatch looks promising but I would want to use MixMatch with the Above() function and I cannot do that in the load script.

Thanks in advance for any assistance.

[giovanneb · Accepted solution]

Hi, try this

tmp:

LOAD * Inline [

Category, Value

category 1, .1

category 2, .3

category 2, .2

category 1, .1

category 2, .3

category 1, .1

category 2, .2

];

tmp1:

NoConcatenate

LOAD

RowNo() as Count,

*

Resident tmp;

tmp2:

NoConcatenate

LOAD

Count,

Category,

Value,

if(Previous(Category) = Category

AND Previous(Value) = Value,

Peek(Occurrence) + 1, 1) as Occurrence

Resident tmp1

Order by Category, Value asc;

Result:

NoConcatenate

LOAD

*

Resident tmp2

Order by Count asc;

DROP Field Count;

DROP Tables tmp1, tmp2, tmp;

Reguards

[Anonymous]

Thank you.  I was also able to get help from this post.

Full Accumulation example

[swuehl]

Another option could be

LOAD *, AutoNumber(recno(),Category & Value) as Occurrence

Inline [

Category, Value

category 1, .1

category 2, .3

category 2, .2

category 1, .1

category 2, .3

category 1, .1

category 2, .2

];