Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

count until value changes (in diagram)

hello and a happy new year to everyone

i've got a question about a counting function:

how can i count the rows until the value in one column changes.

ex:

A

A

A

A

A

A

B

B

B

A

A

A

A

i want to count all "A's" before the value changes to "B"

thx for your help!

1 Solution

Accepted Solutions
sunny_talwar

Script:

Table:

LOAD *,

  If(Peek('Example') = Example, Peek('Same'), RangeSum(1, Peek('Same'))) as Same;

LOAD * Inline [

Example

A

A

A

A

B

B

B

B

A

A

B

B

B

];

Straight Table

Dimension: Example and Same

Expression: Count(Example)

Capture.PNG

View solution in original post

10 Replies
sunny_talwar

Where are you wanting to count this? Script or front end? And you only want to count As? and not Bs

Not applicable
Author

i think i will count on diagram. but it can be in script too.

yes, i have to know how much rows are there before the first B is in. I want to count the Bs as well, but only the uninterrupted Bs.

Example

A

A

A

A

B

B

B

B

A

A

B

B

B

In this example i would like to count the first As, then the First Bs, then de second As and the second Bs - ist that even possible?

sunny_talwar

Script:

Table:

LOAD *,

  If(Peek('Example') = Example, Peek('Same'), RangeSum(1, Peek('Same'))) as Same;

LOAD * Inline [

Example

A

A

A

A

B

B

B

B

A

A

B

B

B

];

Straight Table

Dimension: Example and Same

Expression: Count(Example)

Capture.PNG

MarcoWedel

Hi,

maybe also helpful:

QlikCommunity_Thread_198363_Pic3.JPG

QlikCommunity_Thread_198363_Pic2.JPG

QlikCommunity_Thread_198363_Pic4.JPG

table1:

LOAD *,

    AutoNumber(ID,ClusterTotNum) as field1Seq,

    AutoNumber(ClusterTotNum,field1) as ClusterNum;

LOAD RecNo() as ID,

    *,

    Alt(Peek(ClusterTotNum),0)-(Previous(field1)<>field1) as ClusterTotNum

Inline [

field1

A

A

A

A

B

B

B

B

A

A

B

B

B

A

A

A

A

A

A

B

B

B

A

A

A

A

];

Join

LOAD ClusterTotNum,

    Count(field1) as ClusterSize

Resident table1

Group By ClusterTotNum;

Not applicable
Author

thank you! this will help me!

Not applicable
Author

thank you! this will help me!

MarcoWedel

you're welcome.

If you like, you can mark this solution helpful.

thanks

regards

Marco

Not applicable
Author

Great! very helpful.

what if I want to count the number of clusters with the same label, output example:

A: 4

B: 3

Thanks!

Not applicable
Author

I figured it out by using the expression as max(ClusterNum):

cluster_num.JPG

This is very useful to count the number of clusters.