Bringing back last value

[MassicotPSCU]

I have a field called Activity ID and a field called Activity Receipt. Each Activity ID can have multiple values for the Activity Receipt field. Each value for the Activity Receipt field will be either open, closed or invalid.

For each activity ID, I need to pull back all the activity receipts with an invalid value and only the last Activity Receipt value that is not invalid. In the screenshot below, I would pull back every record except the first one with the open receipt. I've tried using last value and the group by in the scripts, but it still brings back everything. Is there any way to do this?

[BrunPierre]

What should be the expected output?

[NiTo]

Hi,

To understand it better, you want to pull out Activity receipt with status "OPEN" and "INVALID" and ignore the "Closed" status. Did I understand it correctly?

[do_Ob]

I think you need an extra column to show how they should be sorted. Now, there is no way to tell whether you should pick the 'Open'  or the 'Closed'. If you name that column ID, then something like this should work as a Dimension:

=if(ActivityReceipt='Invalid' or ActivityReceipt<>'Invalid' and ID=aggr(NODISTINCT max(ID), ActivityID)
, ActivityID)

and deselect 'Include Null Values '

[do_Ob]

Sorry, there was an omission in my syntax; which produced the wrong results when the last Receipt is an 'Invalid' 

If I use this as input: 

Activities:
Load
*,
RowNo() as ID
;
Load * Inline [
ActivityID, ActivityReceipt
10009, Open
10009, Invalid
10009, Invalid
10009, Invalid
10009, Closed
10010, Open
10011, Open
10011, Closed
10012, Open
10012, Invalid];

Then the syntax should be:

=if(ActivityReceipt='Invalid' or ID=aggr(NODISTINCT max({<ActivityReceipt ={'Open', 'Closed'}>} ID), ActivityID)
, ActivityID)

And exclude null-values.