Count duplicate values

Thu, 22 Mar 2012 14:26:21 GMT

Hello everybody!

how can i count duplicate values in the script?

Example:

MyTable

After counting i need this output:

Thank You!

Re: Count duplicate values

Thu, 22 Mar 2012 17:56:41 GMT

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, 3

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!