How to select the latest Stock Price (based upon date field) from table of Sales transactions

[#1]

Good morning all,

New user (I know..don't sigh) and I am using V8.5 developer.

Table I am extracting data from:

Name=StockTrans

Fields=SalesID, SalesDate, StockItem, SalesPrice

Example of data:

SalesID SalesDate StockItem SalesPrice

1 01/12/2009 100 15.00

2 05/12/2009 100 13.00

3 10/12/2009 100 14.00

4 15/01/2010 200 2.35

5 01/12/2009 200 1.95

6 25/01/2010 200 9.95

What I want in my table is:

SalesID SalesDate StockItem SalesPrice

3 10/12/2009 100 14.00

6 25/01/2010 200 9.95

I am after the SalesPrice for each StockItem based upon the greatest SalesDate for each StockItem.

Thanks

James

[#2]

Hi James, you can subtitute the SalesDate dimmension for a calculated dimmension, that might look something like this:

=aggr(max(SalesDate),StockItem)

Regards

[#3]

Hi James,

please see the attached example (script solution).

Good luck!

Rainer

[#4]

Ivan,

Thank you for the suggestion.

I have tried as you suggested and this works to a certain extent.

1) Created Chart Table.

2) Created the =aggr(max(SalesDate),StockItem) as a Calculated Dimension

3) Created expression =StockItem

If I leave the chart table like this is returns a single line with the correct date = PERFECT!!!!!!!

HOWEVER I need the SalesPrice associated with this date. If I add in the sales price as either a Dimension or expression the chart table then shows all SalesPrice values for the stock code (there are different values on different days).

Any idea how I can now just get the SalesPrice associated with the =aggr(max(SalesDate),StockItem)????

Regards

James

[#5]

I see, the aggr expression did not work as I was expecting. See the attached file with you issue solved, however I think Rainer solution might be a little bit cleaner.

Regards.

[#6]

Hi, I came up with another solution I do not know if it is faster or cleaner, it is just another approach. The script is little dirty because I could not use the order by statement within the load inline, but I am pretty sure you would when first loading the real data.

Regards

[wizardo]

hi there

i tried doing it differently using mapping

i put the script here so those with personal edition can try

i took ivan_cruz's script and slightly altered it

Sales_temp:
Load * INLINE [
SalesID, SalesDate, StockItem, SalesPrice
1, 01/12/2009, 100, 15.00
2, 05/12/2009, 100, 13.00
3, 10/12/2009, 100, 14.00
4, 15/01/2010, 200, 2.35
5, 01/12/2009, 200, 1.95
6, 25/01/2010, 200, 9.95
];

mapping
load
StockItem,
date(max(SalesDate),'DD/MM/YYYY') as maxdate
resident Sales_temp
group by StockItem;


Sales:
noconcatenate
load *
resident Sales_temp
where SalesDate = applymap('Sales_temp',StockItem);

drop table Sales_temp;