topic find duplicated rows? in QlikView

find duplicated rows?

Wed, 30 Mar 2011 12:51:51 GMT

Hello all

I'm new in QV. Lets say i have table with 2 fields. 1 is country and 1 is postal code inside. Some country names duplicated because of postal codes. other countries name without postal code didn't duplicated. Now i need to make new field contains 1 to duplicated country names and 0 to non duplicated country names.

How to do this. Please help

Regards.
Muncho

find duplicated rows?

Wed, 30 Mar 2011 13:13:14 GMT

Hello Muncho,

This can help you ?

Table1:

Load * Inline
[Country, PostalCode
COUNTRY 1, 1000
COUNTRY 2, 1500
COUNTRY 2, 2000
COUNTRY 3, ];

Table2:
Load
Country,
Count( Country) -1 as Flag
resident Table1
Group By Country;

Table3:
Noconcatenate Load * Resident Table1;
left join Load * Resident Table2;

drop table Table1;
drop table Table2;

Regards,

Beyrem

find duplicated rows?

Thu, 31 Mar 2011 08:26:26 GMT

hello Beyrem

thanks for your post. but its not helping. because in your example there are only 3 countries inside and count them and -1 as flag so its giving 0 and 1. add more country names and reload your example you can see the problem.
there is about 400 duplicated and not duplicated county names inside my data and it's not giving me right result.

Muncho

find duplicated rows?

Thu, 31 Mar 2011 09:01:10 GMT

i fount 1 way to do this. its using mapping load. mapping load en, an, nm 1; apply map country so on.but its doing manually. is there any other automagical function or something to do this?

find duplicated rows?

Thu, 31 Mar 2011 09:30:37 GMT

Hello Muncho

This Could be better ?

Table1:

Load * Inline
[Country, PostalCode
COUNTRY 1, 1000
COUNTRY 2, 1500
COUNTRY 2, 2000
COUNTRY 2, 2500
COUNTRY 2, 3000
COUNTRY 3, ];

Table2:
Load
Country,
if(Count( Country)>1,1,0) as Flag
resident Table1
Group By Country;

Table3:
Noconcatenate Load * Resident Table1;
left join Load * Resident Table2;

drop table Table1;
drop table Table2;

regards,

Beyrem

find duplicated rows?

Thu, 31 Mar 2011 10:53:18 GMT

tack så mycket