Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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!

Tags (2)
1 Solution

Accepted Solutions

Re: count until value changes (in diagram)

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

10 Replies

Re: count until value changes (in diagram)

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

Not applicable

Re: count until value changes (in diagram)

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?

Re: count until value changes (in diagram)

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

Re: count until value changes (in diagram)

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

Re: count until value changes (in diagram)

thank you! this will help me!

Not applicable

Re: count until value changes (in diagram)

thank you! this will help me!

Re: count until value changes (in diagram)

you're welcome.

If you like, you can mark this solution helpful.

thanks

regards

Marco

Not applicable

Re: count until value changes (in diagram)

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

Re: count until value changes (in diagram)

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

cluster_num.JPG

This is very useful to count the number of clusters.

Community Browser