topic Create ascending count variable in QlikView

Create ascending count variable

pepe2209 — Tue, 17 Jun 2014 08:34:32 GMT

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

Re: Create ascending count variable

Gysbert_Wassenaar — Tue, 17 Jun 2014 08:37:45 GMT

MyTable

load Maingroup, Subgroup, Date,

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

from myqvdfile.qvd (qvd);

Re: Create ascending count variable

ashfaq_haseeb — Tue, 17 Jun 2014 08:43:26 GMT

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

Re: Create ascending count variable

tresB — Tue, 17 Jun 2014 08:44:53 GMT

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;