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
davidtottyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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.
0
davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
There is a union too much at the end, also you can't have a global where clause, this would only work on the last union.

You better get this subset first, eg

SELECT * FROM "K:\SSS\S90\S-COIM01.DBF" WHERE CE_PONO = ( HSPO1 ) INTO CURSOR S_COIM01, then do the UNION query.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
davidtottyAuthor Commented:
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
0
Olaf DoschkeSoftware DeveloperCommented:
OK, fine. Glad it helps.
0
davidtottyAuthor Commented:
It certainly did help Olaf. I've learned a lot.

Thanks for all your help.

David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.