topic Re: Count number of same value clusters in QlikView

Count number of same value clusters

Fri, 10 Feb 2017 12:30:00 GMT

I want to count the number of clusters having a certain value. Ideally as a chart expression but if that's not possible then the load script would be fine.

Ex if I have a table with a Field:

I want to get:

A: 3

B: 2

Re: Count number of same value clusters

Anil_Babu_Samineni — Fri, 10 Feb 2017 16:20:39 GMT

I didn't follow you. Can you provide more information. How you are calc. 3 and 2 for A & B

Re: Count number of same value clusters

Fri, 10 Feb 2017 19:32:02 GMT

A cluster in this case is considered a series of continuous values, and the next cluster starts when the field value changes:

first cluster: AAA (label A)

Second cluster: BBBB (label B)

Third cluster: AA (label A)

Fourth cluster: BBB (label B)

Fifth cluster: A (label A)

As you can see there are three clusters with type A, and two types B. So instead of counting each single row, I want to see how many "blocks" there are for each type. A simple aggregation does not work in this case. I could add a third cluster below, let's say CCCCCC. Doing so would generate the result:

A: 3

B: 2

C: 1

Hope this helps clarify.

Re: Count number of same value clusters

vishsaggi — Fri, 10 Feb 2017 21:37:22 GMT

Try this?

TableA:

LOAD *, if(FieldA = Previous(FieldA), Peek(Rank), RowNo()) as Rank INLINE [

FieldA

];

Add a straight table.

Dim: FieldA

Expr: Count(Distinct Rank)

Re: Count number of same value clusters

Sat, 11 Feb 2017 12:57:30 GMT

Looks great thanks!

Is there any way to do this directly in the straight table expression since FieldA is already loaded, without doing another load in the script?