Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Highlighted
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