Count duplicate values

[#1]

Hello everybody!

how can i count duplicate values in the script?

Example:

MyTable

Col 1	Col 2
a	1
a	2
a	2
a	3
b	1
b	1

After counting i need this output:

Col 1	Col 2	Col 3
a	1	1
a	2	2
a	3	1
b	1	2

Thank You!

[#2]

The easiest way I saw to do this was to create a key of Col1&Col2.  That way you can perform a count on that key and store the result as Col3.

In the script I did:

Table1:

LOAD * INLINE

[

Col1, Col2

a, 1

a, 2

a, 2

a, 3

b, 1

b, 1

];

Table2:

LOAD *,

Col1&Col2 as ColKey,

' ' as Junk1

Resident Table1

;

Drop table Table1;

Table3:

LOAD Col1,

           Col2,

     Count(ColKey) as Col3

Resident Table2

GROUP BY ColKey,Col1,Col2;

Drop table Table2;

The resulting set desired is in Table3.

I also have attached a .qvw in case you need extra reference.

Hope this helps!