topic Re: Count by values in QlikView

Count by values

Anonymous — Tue, 30 Jan 2018 13:25:04 GMT

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

Re: Count by values

rubenmarin — Tue, 30 Jan 2018 17:46:30 GMT

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;

Re: Count by values

sunny_talwar — Tue, 30 Jan 2018 18:01:19 GMT

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;

Re: Count by values

vishsaggi — Tue, 30 Jan 2018 18:23:55 GMT

Another expression using Ruben's Peek().

Check this.

Table1:

LOAD * INLINE [

];

LEFT JOIN(Table1)

Table2:

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

Resident Table1

Order By XX;