Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count by values

Hi community,

I need to calculate a counting in a simple table chart using the values of one the columns, which values are not unique.

I want a result that look like the following example

XX

Counting

A

1

A

2

A

3

B

1

B

2

C

1

D

1

D

2

I tried to use rowno(TOTAL) but that is not the solution for my needs.

I will really appreciate any hint to solve that

Thanks in advance.

Ivan

1 Solution

Accepted Solutions
rubenmarin

Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.

In script you can add this counter using a sorted load and Peek() or Previous():

LOAD XX,

  If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting

Resident TableName

Order By XX, OtherSortingFields;

View solution in original post

3 Replies
rubenmarin

Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.

In script you can add this counter using a sorted load and Peek() or Previous():

LOAD XX,

  If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting

Resident TableName

Order By XX, OtherSortingFields;

sunny_talwar

Although not as efficient as Peek()/Previous() pointed out by rubenmarin‌, but AutoNumber is another possible solution for the script

LOAD XX,

     AutoNumber(RowNo(), XX) as Counting

Reisdent ....

Order by XX;

vishsaggi
Champion III
Champion III

Another expression using Ruben's Peek().

Check this.

Table1:

LOAD * INLINE [

XX

A

A

A

B

B

C

D

D

];

LEFT JOIN(Table1)

Table2:

LOAD *, IF(XX = Previous(XX), Peek(Counting)+1, 1) AS Counting

Resident Table1

Order By XX;