I have the following table with the shown data in it:
send_date | household_ID
11-20-2014 | 123
11-20-2014 | 456
11-15-2014 | 789
I need to do 2 things:
1) Calculate the max value for send_date
2) Filter out any rows whose send_date does not match that value
In other words, I want the output to be:
send_date | household_ID
11-20-2014 | 123
11-20-2014 | 456
Row number 3 should be filtered out as its send_date is not the max.
I tried creating an aggregate, grouping by all columns, and creating a new output port called MAX_DATE with an expression of MAX(SEND_DATE), then have a filter transformation with the condition MAX_DATE = SEND_DATE
This lets all rows through, though. What can I do to make this work....