topic Running count of occurrences in load script in App Development

Running count of occurrences in load script

Anonymous — Wed, 05 Sep 2018 17:37:38 GMT

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.

Re: Running count of occurrences in load script

giovanneb — Wed, 05 Sep 2018 20:00:31 GMT

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

Re: Running count of occurrences in load script

Anonymous — Wed, 05 Sep 2018 20:02:43 GMT

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

Full Accumulation example

Re: Running count of occurrences in load script

swuehl — Wed, 05 Sep 2018 20:56:18 GMT

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

];