Link to home
Start Free TrialLog in
Avatar of davidtotty
davidtotty

asked on

Remove duplicates values.

Hi,

I have a table that contains a number of fields containing a list of countries. Some of them are duplicates. For example:
ITALY , GERMANY , FRANCE , GERMANY , BULGARIA , ITALY , ITALY , GERMANY , BELGIUM , SOUTH AFRICA , ITALY , UNITED KINGDOM

I want to scan the file and remove the duplicate countries so that it would read something like this:
ITALY , GERMANY , FRANCE , BULGARIA , BELGIUM , SOUTH AFRICA , UNITED KINGDOM

I'm being stupid and cannot think of an easy way to do this.

Can someone please suggest something?

Thanks

David
azzcoutmp.xls
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Each field one country?

Then an easy way processing each record would be

lcList = ","
FOR lnFieldno = 1 TO FCOUNT()
   lcCountry = Alltrim(Evaluate(FIELD(lnFieldno)))
   lcList = STRTRAN(","+lcCountry+",",",") && remove country, if it already exists in the list
   lcList = lcList+lcCountry+"," && append it (again)
ENDFOR
lcList = SUBSTR(lcList,2,LEN(lcList)-2) && remove start and end comma

Open in new window


There are details to fix, eg not all fields might be country fields, so the loop needs to start at something else than 1 or end before the last field. Most probably the whole problem is easier fixed beforehand, eg in the creation of this table.

Bye, Olaf.
Avatar of davidtotty
davidtotty

ASKER

Hi Olaf,

I attached an Excel spreadsheet which was exported from the table, just as an example. I produced that table by scanning another table like this:

IF TRIM(COAZTMP.M_COOVFREC) = "Y"
	SELECT S_AOIM02.M_SSUPCRY1, S_COIM01.F301, S_COIM01.F302, S_COIM01.F303, S_COIM01.F304,;
		S_COIM01.F305, S_COIM01.F306, S_COIM01.F307, S_COIM01.F308, S_COIM01.F309,;
		S_COIM01.F310, S_COIM01.F311, S_COIM01.F312, S_COIM01.F313, S_COIM01.F314,;
		S_COIM01.F315, S_COIM01.F316, S_COIM01.F317;
		FROM ;
		K:\SSS\VS90.PRG\COAZTMP.DBF ;
		INNER JOIN "K:\SSS\S90\S-COIM01.DBF" S_COIM01 ;
		ON  COAZTMP.M_PONUM = S_COIM01.CE_PONO;
		WHERE  S_COIM01.CE_PONO = ( HSPO1 );
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP.DBF
ENDIF

Open in new window


Would I be better to try and stop the duplicate countries being entered into the new table at this point? (If that made any sense at all)

David
The source table K:\SSS\S90\S-COIM01.DBF already has the duplicates in it, doesn't it. From what process is that coming from? You need to go all the way back to where the duplicates come in the first time, if you want to have an easier fix.

It's always easier to remove duplicates when they come in rows and not columns, as you then can simply use DISTINCT or GROUP BY.

Bye, Olaf.
Yes that's right that table already has the duplicates in it. They are for a list of manufactures and the counties of manufacture. Like this:

Field f101      
WARMALD ANSUL UK LTD, STOCKPORT TRADING ESTATE, YEW STREET, STOCKPORT,      

Field f102
CHESHIRE, UK.      

Field f103
UNITED KINGDOM

There are up to eighteen suppliers but sometimes only two or three.

I only need the countries for my current task.

Yes I thought the same as you. It is easier to remove them by rows rather than columns. I did start trying do do it with IF statements but that just got clumsy.

Hopefully you can suggest something better.

Thanks

David
Are countries only in f103 fields? I don't assume from the query you gave there are several fields.

Something like a union would also remove duplicates eg

SELECT f103 as country FROM table
UNION
SELECT f203 FROM table
UNION
SELECT f303 FROM table
etc.

For example:

Create Cursor crsTest (f103 C(10), f203 C(10), f303 C(10))
Insert into crsTest Values ("ITALY","GERMANY","UK")
Insert into crsTest Values ("GERMANY","BULGARIA","MEXICO")
Insert into crsTest Values ("MEXICO","UK","USA")
Select f103 as country from crsTest;
union;
Select f203 from crsTest;
union;
Select f303 from crsTest

Open in new window


UNION does remove duplicates if you don't UNION ALL.

If you have a source table with countries in a single field it's simply
SELECT DISTINCT fxyz as country FROM table

Open in new window


Bye, Olaf.
Thanks Olaf,

So I need to do something like this:

SELECT M_SSUPCRY1 AS COUNTRY FROM S_AOIM02;
		UNION;
		SELECT F301 FROM S_COIM01;
		UNION;
		SELECT F302 FROM S_COIM01;
		UNION;
		SELECT F303 FROM S_COIM01;
		UNION;
		SELECT F304 FROM S_COIM01;
		UNION;
		SELECT F305 FROM S_COIM01;
		UNION;
		SELECT F306 FROM S_COIM01;
		UNION;																														
		K:\SSS\VS90.PRG\COAZTMP.DBF ;
		INNER JOIN "K:\SSS\S90\S-COIM01.DBF" S_COIM01 ;
		ON  COAZTMP.M_PONUM = S_COIM01.CE_PONO;
		WHERE  S_COIM01.CE_PONO = ( HSPO1 );
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP.DBF

Open in new window


Or have I got that completely wrong?

David
You can't use the table alias you define at last.

You better USE "K:\SSS\S90\S-COIM01.DBF" ALIAS S_COIM01 before doing a union query. Inner join is not need4ed at all in this case, you don't want to put further columns aside of the others, you want a clean list of countries.

If you need this per PONUM then this wouldn't work out at all this way.

Bye, Olaf.
OK I think I've got it now. So I need to do something like this:

HSPO1 = "AZF-006519"
USE "K:\SSS\S90\S-COIM01.DBF" ALIAS S_COIM01
	SELECT F301 AS COUNTRY FROM S_COIM01;
		UNION;
		SELECT F302 FROM S_COIM01;
		UNION;
		SELECT F303 FROM S_COIM01;
		UNION;
		SELECT F304 FROM S_COIM01;
		UNION;
		SELECT F305 FROM S_COIM01;
		UNION;
		SELECT F306 FROM S_COIM01;
		UNION;
		SELECT F307 FROM S_COIM01;
		UNION;
		SELECT F308 FROM S_COIM01;
		UNION;
		SELECT F309 FROM S_COIM01;
		UNION;
		SELECT F310 FROM S_COIM01;
		UNION;
		SELECT F311 FROM S_COIM01;
		UNION;
		SELECT F312 FROM S_COIM01;
		UNION;
		SELECT F313 FROM S_COIM01;
		UNION;
		SELECT F314 FROM S_COIM01;
		UNION;
		SELECT F315 FROM S_COIM01;
		UNION;
		SELECT F316 FROM S_COIM01;
		UNION;
		SELECT F317 FROM S_COIM01;
		UNION;
		SELECT F318 FROM S_COIM01;
		UNION;
		WHERE  S_COIM01.CE_PONO = ( HSPO1 );
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP.DBF

Open in new window


Or am I still not getting it?

Sorry to be such a pain.

David
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK I think I'm getting a better understanding now. What I need to do is create a table with the countries that match HSPO1 from the S-COIC01 table. Then do the UNION query.

Like this:
IF TRIM(COAZTMP.M_COOVFREC) = "Y"
	SELECT S_AOIM02.M_SSUPCRY1, S_COIM01.F301, S_COIM01.F302, S_COIM01.F303, S_COIM01.F304,;
		S_COIM01.F305, S_COIM01.F306, S_COIM01.F307, S_COIM01.F308, S_COIM01.F309,;
		S_COIM01.F310, S_COIM01.F311, S_COIM01.F312, S_COIM01.F313, S_COIM01.F314,;
		S_COIM01.F315, S_COIM01.F316, S_COIM01.F317;
		FROM ;
		K:\SSS\VS90.PRG\COAZTMP.DBF ;
		INNER JOIN "K:\SSS\S90\S-COIM01.DBF" S_COIM01 ;
		ON  COAZTMP.M_PONUM = S_COIM01.CE_PONO;
		WHERE  S_COIM01.CE_PONO = ( HSPO1 );
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP.DBF
ENDIF
***
	USE K:\SSS\VS90.PRG\AZZCOUTMP.DBF ALIAS COUTMP
	SELECT EXP_1 AS COUNTRY FROM COUTMP;
		UNION;
		SELECT F301 FROM COUTMP;
		UNION;		
		SELECT F302 FROM COUTMP;
		UNION;
		SELECT F303 FROM COUTMP;
		UNION;
		SELECT F304 FROM COUTMP;
		UNION;
		SELECT F305 FROM COUTMP;
		UNION;
		SELECT F306 FROM COUTMP;
		UNION;
		SELECT F307 FROM COUTMP;
		UNION;
		SELECT F308 FROM COUTMP;
		UNION;
		SELECT F309 FROM COUTMP;
		UNION;
		SELECT F310 FROM COUTMP;
		UNION;
		SELECT F311 FROM COUTMP;
		UNION;
		SELECT F312 FROM COUTMP;
		UNION;
		SELECT F313 FROM COUTMP;
		UNION;
		SELECT F314 FROM COUTMP;
		UNION;
		SELECT F315 FROM COUTMP;
		UNION;
		SELECT F316 FROM COUTMP;
		UNION;
		SELECT F317 FROM COUTMP;
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP2.DBF

Open in new window


I must be doing something wrong though as I keep getting a message saying:

K:\SSS\VS90.PRG\AZZCOUTMP2.DBF already exists. Overwrite it?

Any Ideas?

I do appreciate all your help Olaf.

David
Well, that's to be expected if you repeatedly create a table. You better change the target name, as the result may not be what you want to overwrite your previous result.

Bye, Olaf.
I guess it helps if I read your post properly. If I do the below I get a table with no duplicates.

	SELECT * FROM "K:\SSS\S90\S-COIM01.DBF" WHERE CE_PONO = ( HSPO1 ) INTO CURSOR COUTMP
	BROWSE 
	SELECT F301 AS COUNTRY FROM COUTMP;
		UNION;
		SELECT F302 FROM COUTMP;
		UNION;
		SELECT F303 FROM COUTMP;
		UNION;
		SELECT F304 FROM COUTMP;
		UNION;
		SELECT F305 FROM COUTMP;
		UNION;
		SELECT F306 FROM COUTMP;
		UNION;
		SELECT F307 FROM COUTMP;
		UNION;
		SELECT F308 FROM COUTMP;
		UNION;
		SELECT F309 FROM COUTMP;
		UNION;
		SELECT F310 FROM COUTMP;
		UNION;
		SELECT F311 FROM COUTMP;
		UNION;
		SELECT F312 FROM COUTMP;
		UNION;
		SELECT F313 FROM COUTMP;
		UNION;
		SELECT F314 FROM COUTMP;
		UNION;
		SELECT F315 FROM COUTMP;
		UNION;
		SELECT F316 FROM COUTMP;
		UNION;
		SELECT F317 FROM COUTMP;
		INTO TABLE K:\SSS\VS90.PRG\AZZCOUTMP.DBF
		BROWSE 
ENDIF

Open in new window


Now I just need to populate a field with the countries in, add columns and remove the blanks.

Thanks Olaf
OK, fine. Glad it helps.
It certainly did help Olaf. I've learned a lot.

Thanks for all your help.

David