Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thank you. I was also able to get help from this post.
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
];