How to: Remove highest price item when more than one found 4

[keepingbusy]

Hi all

Here's the scenario:

We have a table that used to have a couple of thousand records / titles but that has now increased to over 400,000. I am currently using the code below to search for any titles within the table that match by way of UPC number. This is a unique number that relates to the title of a CD / DVD etc for example: 012345678910 and is stored in character field. As there is usually more than one of the same UPC number (after merging three tables) we only need to remove the highest price item(s) and just keep the single lowest one, example:

Kelly Clarkson - Greatest Hits - £14.99 - 01234567
Kelly Clarkson - Greatest Hits - £13.99 - 01234567
Kelly Clarkson - Greatest Hits - £11.45 - 01234567

Needles to say we want to delete the top two and just keep the £11.45 one. This works with a few thousand records quite quickly but with 400,000, well....

tempfile=SYS(3)  && Create temp file
USE MAINCAT EXCLUSIVE
	
COPY STRU TO tempfile+'.DBF'
COPY STRU TO NEWFILE.DBF
	
CLOSE DATABASES

USE tempfile+'.DBF' EXCL
APPEND FROM MAINCAT
DELE FOR EMPTY(UPC) && Remove empty recs
PACK
GO TOP

A=1
DO WHILE NOT EOF()
  REPLACE URN WITH A
  A=A+1
  SKIP
ENDDO

GO TOP
	
mreccount=0
mreccount=RECCOUNT()

DO WHILE NOT EOF()
  mlowest=SYS(3)
  STORE SPACE(18) TO mupc
  STORE UPC TO mupc
		
  mthis=0
  mthis=RECNO()

  CLOSE DATABASES

* Select all records matching UPC number
* regardless of price

  SELECT * FROM tempfile+'.dbf' WHERE;
   UPC=mupc ORDER BY SELL INTO TABLE mlowest+'.DBF'
  GO TOP

  murn=0
  murn=URN
  DELE FOR URN<>murn
  PACK
		
  CLOSE DATABASES
  USE NEWFILE EXCL
  APPEND FROM mlowest+'.DBF'
		
  CLOSE DATABASES
  DELE FILE mlowest+'.dbf'
		
  USE tempfile+'.dbf' EXCL
  DELE FOR UPC=mupc
  GO mthis
  SKIP
ENDDO	

CLOSE DATABASES
	
USE tempfile+'.DBF' EXCL
PACK

CLOSE DATABASES

CLEAR
SET SAFETY OFF

CLOSE DATABASES

USE MAINCAT EXCL
ALTER TABLE MAINCAT DROP COLUMN URN
ZAP
APPEND FROM tempfile+'.dbf'
PACK

* .....Done etc

Can anyone please suggest an easier / quicker way to achieve a faster result?

Many thanks in anticipation

Lee


Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[jesylv]

You could try something like:

 Select UPC, min(price) AS minPrice, Description from catalogue into cursor NewCatalogue group by UPC, Description

Jean

http://www.sigp.ca

 

[Mike Lewis]

Lee,

How about something like this (off the top of my head -- not tested):

First, add a unique ID field to each record. You could use the RECNO() for this. Call this new field ID.

Once you've added this field to the structure, you can populate it like so:

SELECT Maincat
RELACE ALL ID WITH RECNO()

Next, do something like this:

SELECT ID, MIN(Price) AS MinPrice ;
  FROM MainCat GROUP BY ID ;
  INTO CURSOR Temp

DELETE FROM  MainCat WHERE ;
  ID IN (SELECT ID FROM temp)

You'd need VFP 9.0 for that last line, but that's not a problem for you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Mike Lewis]

Mike,

... He wants to keep the lowest price ...

You're right, of course.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[keepingbusy]

All

Thank you for your posts. For those looking for a similar sollution, our problem has been solved with a slight alteration using the following:

USE Maincat EXCL

ALTER TABLE maincat ADD COLUMN id N(10)

REPLACE ALL ID WITH RECNO()

SELECT artist, title, catalog_nu, available, ;
  sell, config, upc, type, format, streetdate, ;
  tempcode, ID, MIN(SELL) AS MinPrice ;
  FROM MainCat GROUP BY ID ;
  INTO TABLE tempfile+'.DBF'

DELETE FROM tempfile+'.DBF' WHERE ;
  ID NOT IN (SELECT ID FROM tempfile+'.DBF')

Thank you for taking the time to make some suggestions.

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[Olaf Doschke]

Hmm, you used VFP6 for this, right? Or SET ENGINEBEHAVIOR 7, otherwise the Group By wouldn't have worked.

Only gropuing by ID is also not what you wanted, this would only simply find the one record with the lowest price of all items, wouldn't it?

I think you want the lowest price for each UPC, right?

So If you have

Kelly Clarkson - Greatest Hits - £14.99 - 01234567
Kelly Clarkson - Greatest Hits - £13.99 - 01234567
Kelly Clarkson - Greatest Hits - £11.45 - 01234567
The Beatles - The White Album - £16.99 - 76543210
The Beatles - The White Album - £13.15 - 76543210
The Beatles - The White Album - £14.99 - 76543210

You want to end up with:

Kelly Clarkson - Greatest Hits - £11.45 - 01234567
The Beatles - The White Album - £13.15 - 76543210

Then you need each whole record with the minimum price of each group of records with the same UPC.

Select T1.* From maincat T1;
Where T1.price = ;
 (Select min(T2.price) From maincat T2 ;
  Where T2.upc = T1.upc)

Bye, Olaf.

 

[keepingbusy]

Ooops, spoke to soon I think. Just ran a check with the 500,000+ records and I'm back to square one. There are still duplicates showing with the same UPC number.

Any suggestions now guys?

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Hi Olaf

Yes, as you have suggested, that was what I'm looking for. Just made a slight amendment:

Select T1.* From maincat T1;
  Where T1.sell = ;
  (Select min(T2.sell) From maincat T2 ;
  Where T2.upc = T1.upc) [b]INTO TABLE tempfile+'.DBF'[/b]

and ran the code several times. This now appears to be working perfectly, removing the highest price item.

This is once again a perfect example of how great this forum really is and once again I'm grateful to you Olaf and of course, others who posted on this thread.

Thanks a million

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[jesylv]

My kick at the can:

OPTION 1 : MainCat is part of a DBC

*-- Needs to use non standard SQL Syntax
SET ENGINEBEHAVIOR 70
SET SAFETY OFF
local lcbk as string

*-- Change to your backup file
lcbk = "C:\backups\myBackup.dbf"

*-- Make a copy of your table (backup)
SELECT MainCat
COPY TO &lcbk. WITH CDX
*---------------------------------------

*-- Select the records to keep
SELECT MainCat.* MIN(SELL) AS MinPrice ;
   FROM MainCat 
   INTO CURSOR cMyLowestPrices ;
   GROUP BY UPC ;

*-- Delete the catalogue
USE IN MainCat
USE MainCat IN 0 EXCLUSIVE
SELECT MainCat
ZAP
*--------------------------------

*-- Hydrate with lowest priced items
APPEND FROM cMyLowestPrices

*-- Security checks
IF RECCOUNT("MainCat") # RECCOUNT("cMyLowestPrices")
   MessageBox("Error in process. Reverting", 16)
   ZAP
   APPEND FROM &lcbk. 
ENDIF

*-- Add cleanup code here

OPTION 2 : not part of dbc and no cdx

*-- Needs to use non standard SQL Syntax
SET ENGINEBEHAVIOR 70
SET SAFETY OFF
local lcbk as string

*-- Change to your backup file
lcbk = "C:\backups\myBackup.dbf"

*-- Make a copy of your table (backup)
SELECT MainCat
COPY TO &lcbk. WITH CDX
*---------------------------------------

*-- Select the records to keep
SELECT MainCat.* MIN(SELL) AS MinPrice ;
   FROM MainCat 
   INTO CURSOR cMyLowestPrices ;
   GROUP BY UPC ;

ERASE MainCat

SELECT artist, title, catalog_nu, available, ;
  sell, config, upc, type, format, streetdate, ;
  tempcode FROM cMyLowestPrices INTO TABLE MainCat


*-- Security checks
SELECT MainCat
IF RECCOUNT("MainCat") # RECCOUNT("cMyLowestPrices")
   MessageBox("Error in process. Reverting", 16)
   ZAP
   APPEND FROM &lcbk. 
ENDIF

*-- Add cleanup code here

Code is not tested but the concept should work.


Jean

http://www.sigp.ca

 

[keepingbusy]

Hi Jean

Thank you for taking the time to post the above code which looks very impressive. As mentioned, I've resolved the issue but I'm sure someone doing a search on this forum will find all the answers most beneficial.

Thanks again

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Mike Y

Will it make a difference if SET ENGINEBEHAVIOR 70 is removed? (performance etc)

Your views would be appreciated

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[Olaf Doschke]

Hi Lee,

Glad you figered it out, I was not very accurately considering your field names and of yourse selecting INTO TABLE helps.

I only wonder why you had to run my solution multiple times. The SQL-Select determines the minimum price and so only needs to run once.

The only reason, why two or more records of the same UPC could have remained is, that there are several records with the same minimum price for some UPCs. You may have found these and increased one of the prices to get rid of that record.

Regarding ENGINEBEHAVIOR, I can only recommend, what myearwood told you. You can read about changes made in SQL language within the chapters "What's New in VFP8/9", that should cover all questions about why SQL has been restricted, eg to have ANSI compliant GROUP BY clauses.

Bye, Olaf.

 

[keepingbusy]

Mike Y (and all on this post)

After running the code several times that I thought had sorted the issue, I'm still left with some duplicate UPC numbers.

I'm not sure why this happening so I tried your suggestion but came up against a few errors. Perhaps you can suggest a change. In code 2 I get:

Command contains unrecognised phrase/keyword

I did notice that there was a ; missing (I think) but even after inserting one, I get the same error.

SELECT MainCat.* MIN(SELL) AS MinPrice ;
   FROM MainCat [b];[/b]
   INTO CURSOR cMyLowestPrices ;
   GROUP BY UPC

This particular problem is becoming frustrating but I'm sure with a bit of patients I'll get there in the end.

Any other suggestions guys?

Many thanks

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[Olaf Doschke]

There is a comma missing between MainCat.* and MIN(Sell).

 

[keepingbusy]

Hi Olaf

Problem solved with the missing comma

Many thanks

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[jesylv]

Hi Mike,

The reason the Set EngineBehavior 70 is needed is because of the "Group by" clause. This SQL statement should throw an error if Set EngineBehavior is 80 or 90.

Lee, Sorry for the missing comma. I need a new pair of glasses.

Jean

http://www.sigp.ca

 

[keepingbusy]

Hi Jean

No problem, grateful for your help

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Hi Mike Y

Thank you for the above post

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Hi all

I thought this was resolved until a further check was done on some of the titles in our table.

An example of this is:
ANARTIST - MYRECORD UPC no 0165681710016 price £7.60
ANARTIST - MYRECORD UPC no 0165681710023 price £11.99
ANARTIST - MYRECORD UPC no 0165681710023 price £11.25

Ok, so the first one stays because its unique with a different UPC number (thats fine).

However, after running the below code my understanding was that the lowest price item of items 2 and 3 would be removed, e.g. the £11.25 would be removed.

cLowest=SYS(3)
SELECT MainCat.*, MIN(SELL) AS MinPrice ;
  FROM MainCat INTO TABLE cLowest+'.DBF' ;
  GROUP BY UPC

This isn't the case as the £11.99 selection is being kept and the cheaper one (£11.25) is being removed.

I would be grateful for any suggestions why this is going wrong.

Not sure if I mentioned, but I'm using VFP9

Many thanks

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Hi Mike Y

After a slight change to your suggestion and many (many!) tests with different data, the code below has finally resolved our issue:

c_MinPrices=SYS(3)
c_LowestPricedProducts=SYS(3)

SELECT ;
  UPC, ;
  MIN(SELL) as MinPrice ;
  FROM ;
  MAINCAT ;
  GROUP BY ;
  UPC ;
  INTO TABLE ;
  c_MinPrices

SELECT * ;
  FROM ;
  MAINCAT ;
  INNER JOIN ;
  c_MinPrices ;
  ON c_MinPrices.UPC = MAINCAT.UPC ;
  AND c_MinPrices.MinPrice = MAINCAT.SELL ;
  INTO TABLE c_LowestPricedProducts

I am very grateful for your help in this matter. Could you visit our website and send us your email address.

Thanks again

Lee
PS: Thanks to everyone else on this thread, inspiration and suggestion are some of the keys of life.

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk