Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
brianrmacdonald
Contributor

Running count of occurrences in load script

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.

1 Solution

Accepted Solutions
giovanneb
Contributor II

Re: Running count of occurrences in load script

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

View solution in original post

3 Replies
giovanneb
Contributor II

Re: Running count of occurrences in load script

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

View solution in original post

brianrmacdonald
Contributor

Re: Running count of occurrences in load script

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

Full Accumulation example

MVP
MVP

Re: Running count of occurrences in load script

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

];