Scripting Issue - Please Help

[samvile18]

Hello everyone,

I'm having difficulty trying to transform a table of data I have, the script is a simple straight load which gives me data like this;

This is how the flat table looks

I need to transform this so I end up with a unique line for each entry that shows the max completed date and then 2 more fields with next due, next due +1. So it should look like the picture below;

Transformed Table

If anyone can help me out I'd be massively thankful - I'm not a million miles away, I just can't quite get it nailed. 

[Vegar · Accepted solution]

You need to aggregate in some way. Try to create a table using all the dimensions except the two date dimensions. Then for maxCompletedDueDate use this expression 

=MaxString(CompletedDate)

For the due dates try the max function

=Min(DueDate)

=min(DueDate, 2)

[Vegar · Accepted solution]

To exclude the completed you also might need to adjust your expression with a SET modifier. 

=Min({<CompletedDate - ={*} >} DueDate)

=min({<CompletedDate - ={*} >}DueDate, 2)