If you are unable to create a new account, please email support@bspsoftware.com

 

How to fill in a column with data from other columns with a set condition?

Started by EricW, 31 May 2021 11:55:44 PM

Previous topic - Next topic

EricW

I have a dataset like this, which shows who approved a certain report. In my organisation, in some cases(eg.Report ID AB3), reports get rejected by a separate approver(which is out of the scope of this question) and the report is sent back to be edited and resubmitted and there might be a different approver.
    | Approver | Report ID | Action                     |Date    |
    |                | AB3          | Report Archived      |30/12/20|
    | John        | AB3          | Report approved    |27/12/20|
    |                | AB3          | Report Submitted    |26/12/20|
    |                | AB3          | Report Generated   |25/12/20|
    |                | AB3          | Report Resubmitted|24/12/20|
    |                | AB3          | Report Rejected      |23/12/20|
    | Alan        | AB3          | Report approved     |22/12/20|
    |                | AB3          | Report Submitted    |21/12/20|
    |                | AB3          | Report Generated   |20/12/20|
    |                | AB4          | Report archived       |25/12/20|
    | Tommy    | AB4          | Report approved      |21/12/20|
    |                | AB4          | Report Submitted    |20/12/20|
    |                | AB4          | Report Generated   |18/12/20|

However I would like to create a new column with the latest approver's name(by date) for all report IDs by that approver. It should look like this for the end result. In essence I would like to take the latest(date) with the Approver being a non-null value.

    |Latest Approver | Approver | Report ID   | Action                       |Date       | 
    |   John                |                | AB3            | Report archived        |30/12/20|
    |   John                | John        | AB3            | Report approved      |27/12/20|
    |   John                |                | AB3            | Report Submitted      |26/12/20|
    |   John                |                | AB3            | Report Generated     |25/12/20|
    |   John                |                | AB3            | Report Resubmitted  |24/12/20|
    |   John                |                | AB3            | Report Rejected        |23/12/20|
    |   John                | Alan        | AB3            | Report approved       |22/12/20|
    |   John                |                | AB3            | Report Submitted      |21/12/20|
    |   John                |                | AB3            | Report Generated     |20/12/20|
    |   Tommy            |                 | AB4            | Report archived        |25/12/20|
    |   Tommy            | Tommy     | AB4            | Report approved       |21/12/20|
    |   Tommy            |                 | AB4            | Report Submitted      |20/12/20|
    |   Tommy            |                 | AB4            | Report Generated     |18/12/20|

Apologies for the messy table. Thanks in advance!