Data transformation

[Anonymous]

Hi All,

I have a challenge which cannot overcome at the moment. I probably did something like that in the past, but cannot get my head around it at the moment.

I have a log table which holds actions on user accounts in a web portal.

Data which I am interested in is permissions granted or revoked to various applications.

So I did the initial filtering, got the data which I am interested in and got to the point where not sure how to solve it in a best way.

Screenshot below from the data which I would like to transform:

So we have actions per month (did the initial load where I got latest action per month per applicationID and UserID - the Key field is combination of applicationID-UserID).

I want to end up with 2 lines which will have something like Start and End Dates.

First line I know rights to application were granted on Apr-2015 and Revoked on Nov-2015 so these should be start and end dates.

Second I know It was granted again on May-2016 and haven't been Revoked so till today().

Then I want to do some intervalmatch and connect to the rest of model / calendar so I know that this user had access to that app in that month etc.

I don't want to suggest anything, would like to hear you opinions if possible.

Best Regards,

Lukasz