Graphical calculation view to get min and max of a date

[jack_boers]

Hi all,

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).....

Any ideas?

Regards

Jack

[lbreddemann · Accepted solution]

HI Jack

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.

Cheers,

Lars

[jack_boers · Accepted solution]

Many thanks Lars.  I knew the reason, just hadn't met the KEEP flag before