Try this expression: FirstSortedValue(total <[Product ID]> Price,-[Valid from]). Or use it with a group by clause in the script. See attached qvw.
comm69560.qvw 139.0 K
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:
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
LOAD [Product ID2], FirstSortedValue(Price2, -[Valid from2]) as LP2
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.
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.