Find Order status for the latest Order by finding Max date for a spcific product

[#1]

I am trying to display the last order status by product

I am doing it in the load statment like the following

Load

Product,

Max(OrderDate) // can i add if(max(OrderDate)=OrderDate,Status)...it is giving me error message

Resident Table1

Group by Product

[hector · Accepted solution]

Hi, are you trying of just leave the last order date in the model?, because you can do that with Set Analysis too, anyway i guess this is the code that you are looking for

Table1:
Load
 Product,
 OrderDate,
 Status
from ......;

inner join
Load
 Product,
 Max(OrderDate) as OrderDate
Resident Table1
Group by Product

The "secret", is that Table2 will be a table with 2 field, Product & Max(orderdate), but giving the same name (alias) of the original field "OrderDate", this is going to "delete" all the records that are not equal to the last OrderDate

Rgds

PS. In Set Analysis, will be much simpler, it will be something like a table with the dim. Product and the expression Only({<OrderDate = {$(=Max(OrderDate))}>} Status)

[#3]

Thanks Hector, I will try your first suggestion.

Quick Question, will Set Analysis work in the load statment?

[hector]

Quick answer: No, set analysis is just for charts (bar, line.....pivot/straight tables) and every object that supports an expression

Rgds

[#5]

Thanks Hector, your Trick did the Job.