Create ascending count variable

[pepe2209]

Hello

please look at the example:

Maingroup	Subgroup	Date	X
1	A	1	1
1	B	2	1
1	C	3	1
1	C	4	2
2	A	5	1
2	A	6	2
2	A	7	3
2	B	8	1
3	A	9	1
3	B	10	1
3	B	11	2
3	B	12	3
3	C	13	1

I want to add variable X to a qvd which contains Maingroup, Subgroup and Date (Ordered by Maingroup asc, Subgroup asc, Date asc).

The rule for X is that it should count up as long as the values for maingroup and subgroup are the same.

How can I achieve this the best possible way?

your help would be much appreciated.

regards,

Peter

[Gysbert_Wassenaar · Accepted solution]

MyTable

load Maingroup, Subgroup, Date,

if(Maingroup = previous(Maingroup) and Subgroup=previous(Subgroup), 1+peek(X), 1) as X

from myqvdfile.qvd (qvd);

[ashfaq_haseeb]

H, look at the attached application

code:

Temp:

load * , Maingroup & Subgroup as Key;

LOAD Maingroup,

     Subgroup,

     Date

     //X

FROM

[http://community.qlik.com/thread/122241]

(html, codepage is 1252, embedded labels, table is @1);

Main:

Load

  *,

  IF(Key = Previous(Key), Peek('X')+1, 1) as X

Resident Temp

Order By Maingroup, Subgroup,Date;

Drop Table Temp;

Regards

ASHFAQ

[tresesco]

Temp:
LOAD Maingroup,
     Subgroup,
     Date
     //X
FROM
[http://community.qlik.com/thread/122241]
(html, codepage is 1252, embedded labels, table is @1);

NoConcatenate
Final:
Load
*,
If(Maingroup=Peek('Maingroup') and Subgroup =Peek('Subgroup'), Peek('X')+1,1) as X
Resident Temp Order By  Maingroup,Subgroup,Date;

Drop table Temp;