How to calculate price development 4

[german12]

I have a table like this:

As you can see some prices had increased or decreased (more than one line with the same product), and some had not so far (only one line)
In the example these are Bananen and Butter.

I only want to have a new table for things which have changed.

like this
Bananen 1,49 - 1,19 = -0,30 = - 44,7 %
Butter 1,65 - 2,29 = +0,64 = + 38.8 %

I only want to see, where prices got up or down.
Not those with one line - they are unchanged.
This means that some products can have more than one line - that depends how often prices where changed.
(a min/max calculation within a product).

How would you code that?
As I see min/max prices is only possible for the whole table, but not by product.

I can imagine to select a distinct(product) into a 2nd table to have each product only once.
From there perhaps scan that table and compare it with the 1st. table and let a function write into an array per product.

But maybe it is easier perhaps.

Thanks for help
Klaus

Btw - Butter has become very expensive in Germany - the price is for 250 g +39 % within 2 months!
How is that in your country?









Peace worldwide - it starts here...

 

[Chris Miller]

Mike,

I don't know why you do this:

MAX(Datum) AS MinDatum

For the late prices you actually need all prices with Datum later than the minimum date.

So the second query should be:

SELECT p2.PRODUCT, p2.Price, p2.Datum ;
  FROM Prices p2 JOIN ;
  (SELECT p1.PRODUCT, [highlight #FCE94F]MIN[/highlight](Datum) AS MinDatum FROM Prices p1 GROUP BY p1.PRODUCT HAVING COUNT(*) > 1) p1 ;
  ON p2.PRODUCT = p1.PRODUCT AND p2.Datum [highlight #FCE94F]>[/highlight] p1.MinDatum ;
  INTO CURSOR FurtherPrices

Unless you mean to only pick out the latest records, too. But then do MAX(Datum) AS MaxDatum and compare p2.Datum = p1.MaxDatum

Chriss

 

[Mike Lewis]

I don't know why you do this:
MAX(Datum) AS MinDatum

I can explain that. It's called a typing error. Thanks for pointing it out. I've now fixed it.



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Klaus, I think you may have missed something out from my code, that's not the result I get, there seem to be extra products in your example B-)

BANANEN 1KG                     1.49 -          1.19 =          -0.30 =                       -25.2101 % 
BUTTER                          2.09 -          1.65 =          -0.44 =                       -26.6667 % 
BUTTER                          2.29 -          1.65 =          -0.64 =                       -38.7879 % 

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[german12]

Mike Lewis:

Mike - I have tested your amended code - and it works as to be seen above.

Thanks
Klaus



Peace worldwide - it starts here...

 

[Mike Lewis]

Thanks for letting me know, Klaus. Glad it actually worked (finally).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Griff -

I used your code as follows:

CLOSE data
USE prices
*That is the table I mentioned at the beginning
*as you call it mytable I just copied it to mytable

COPY TO mytable
*This is the file I use in your code
*************************************************************

*************************************************************
CLOSE DATABASES
CLEAR



SET DATE german

*Your code starts here:
SELECT 0
USE MYTABLE EXCLUSIVE
INDEX ON MYTABLE.PRODUCT+DTOS(MYTABLE.DATUM) TAG MYINDEX
GO TOP
DO WHILE !EOF()
m.PRODKEY = MYTABLE.PRODUCT
m.PRICE = MYTABLE.PRICE
SKIP
DO WHILE .NOT. EOF() .AND. MYTABLE.PRODUCT= m.PRODKEY
IF MYTABLE.PRICE <> m.PRICE
? MYTABLE.PRODUCT, MYTABLE.PRICE, "-", m.PRICE, "=" ,m.PRICE-MYTABLE.PRICE, "=", ((m.PRICE-MYTABLE.PRICE)/m.PRICE)*100,"%"
m.PRICE = MYTABLE.PRICE
ENDIF
SKIP
ENDDO
ENDDO

with that result now

It is the right calculation, however I can not follow that the result for BUTTER is still different against yours....


I think that has perhaps something to do, that you used DTOS(Datum), which leads to your format for a date (YYMMDD)
However I noted in original our format (german = dd.mm.yy)


Please compare...

Anyway thanks for your comment.
I have to admit, that within my response before there must have been another error caused by myself.

Regards
Klaus


Peace worldwide - it starts here...