topic Running Totals in QlikView

Running Totals

Tue, 21 Jun 2011 20:56:56 GMT

I am looking to count the ID two different ways. The first way is to count the ID and have the field 'Count' change every time the ID changes. The second way is to count 'Count2' the ID and reset after a new ID. I got the Count2 to work with a basic rowno().

Does anyone know a way to get my 'Count' to work? I have displayed an examply below. Thanks everyone.

ID	Count	Count2
1	1	1
1	1	2
1	1	3
2	2	1
3	3	1
3	3	2
4	4	1
4	4	2
5	5	1
6	6	1

Re: Running Totals

nagaiank — Tue, 21 Jun 2011 22:31:40 GMT

I got the other Count also to work to get the result below:

The script used was:

Tab1:

LOAD *, RowNo() as xx;

LOAD * Inline [

101

102

103

104

105

106

];

Tab2:

LOAD *, RowNo() as Count1;

LOAD ID, Sum(xx) Resident Tab1 Group By ID;

Left Join (Tab1) LOAD ID, Count1 Resident Tab2;

DROP Table Tab2;

Tab3:

NoConcatenate LOAD ID, Count1, if(ID <> Peek('ID'),1,Peek('Count2')+1) as Count2 Resident Tab1 Order By xx;

DROP Table Tab1;

Hope this helps.

Re: Running Totals

Fri, 15 Nov 2013 20:11:19 GMT

Clever solution, but can someone suggest how to handle other values in the input data, for example if the input data had ID and value and you still wanted to generate the counters?