How to pick latest price?

[#1]

Hi

How can I add additional column into my script that would show the latest price of the product? I have columns for Product ID, Price and Valid from, and I would like to create additional column that would show the latest price. Please see the situation below.

Product ID	Price	Valid from	Latest price
10001	2,0	01/01/2008	1,8
10002	1,0	01/02/2008	1,4
10002	1,2	01/03/2008	1,4
10003	3,0	01/04/2008	3,5
10001	1,9	01/04/2008	1,8
10002	1,4	01/05/2008	1,4
10001	1,8	01/06/2008	1,8
10003	3,5	01/06/2008	3,5

What I am doing is that I try to build sales forecast. I have sales forecast volume and then I have price list. However in many cases price has been changed once or often and I would like to use always the latest price in my forecast.

I was thinking to do this in script that would that make more sense to do it in expression?

Thanks,

Janne

[#2]

Hi Janne,

either you load only the price from max(date), or if you need all the product history, you load a second table with

product_id, lastprice

This will jopin tables on product_id and you will get LastPrice in every cell for each product.

Regards

Christian

[Gysbert_Wassenaar]

Try this expression: FirstSortedValue(total <[Product ID]> Price,-[Valid from]). Or use it with a group by clause in the script. See attached qvw.

[#4]

Hi Gysbert,

Looks like that your clause in the script seems to work in most of the cases. However, in case that there is only on price in the price table it doesn't anything. Did I do something wrong? I just copied your script

[Gysbert_Wassenaar]

No, the table box won't show duplicate records. Are your other fields company code and customer in the same table? The group by function doesn't include them so there could be duplicates if only product id is considered but the real unique combination is for example product id + company code.

Try for example this script:

T3:
LOAD *
INLINE [
    Product ID2, Price2, Valid from2, Customer
    10001, 2, 01-01-2008,1
    10002, 2, 01-02-2008,1
    10002, 2, 01-03-2008,1
    10002, 2, 01-03-2008,2   
    10003, 3, 01-04-2008,1
    10001, 1.9, 01-04-2008,1
    10001, 1.8, 01-06-2008,1
    10003, 3.5, 01-06-2008,1
    10004, 4.1, 01-03-2008,1   
];

T4:
LOAD [Product ID2], FirstSortedValue(Price2, -[Valid from2]) as LP2
Resident T3
group by [Product ID2];

You won't get a value LP2 for product id 10002, because there are two records that are not unique if you disregard Customer. If you include Customer in the group by clause then you will get a value back.

[#6]

Thanks,

Looks like you are right, there seems to be duplicates and that is probably because I have joined two tables there together. The unique combination includes all fields company code, customer and product. Would you have some easy solution to get rid of these duplicates? I already tried to add clause "where LP > 0" into the resident load, but it didn't worked.

Regards

Janne

[#7]

Hi Jaane

Hope attached QVW will help ......