Graphical calculation view to get min and max of a date
I have a requirement that doesn't seem to work although the idea is simple.
I have a customer, and the customer is in a customer group. The customer has transactions that occur on a date.
The requirement is that I need the Latest transaction date for every customer, and then the Earliest transaction date for every group.
In SQL that would be:
SELECT Group, min(TranDate) AS TranDate FROM (SELECT Group, Customer, max(TranDate) AS TranDate FROM TABLE GROUP BY Group, Customer) GROUP BY Group;
In a Graphical Calc View the sequence should be:
Projection Node with Table
Aggregation Node on Group and Customer, and max on TranDate
Aggregation Node on Group and min on Trandate
Semantics Node with Group and TranDate.
But when I run this I always get the Group and MAX(TranDate).....
Lars Breddemann replied
this is a case for the "KEEP" flag.
Since you don't query for the CUSTOMER column in the final SELECT, the default behavior for the calc engine is to "optimize" it away.
Setting the keep flag to TRUE for "CUSTOMER" on the level of the first aggregation node should do the trick here.