Rewrite Null Value to Correct Value

[Anonymous]

Hello QC.

I have a list of Employee information that contains ID, StartDate, and TerminationDate. We have some bad data caused by a system transition a number of years ago that we're hoping to fix over time, but need a way to get around it in the interim.

Table looks like this:

ID	SystemDate	StartDate	TerminationDate
1	9/30/2017	1/1/2017	10/15/2017
1	4/1/2017	1/1/2017	12/31/2017
1	12/25/2016	1/1/2017	-
2	1/15/2017	2/1/2017	5/31/2017
3	5/10/2015	6/1/2015	-
3	7/15/2016	7/21/2016	7/21/2017
4	7/20/2016	8/1/2016	-
4	10/1/2017	8/1/2016	12/31/2017

For Records that have a matching ID and StartDate, I'd like to overwrite the Null TerminationDate with whatever Non Null value is in the other record that corresponds with the MAX System Date (or most recent system date). But in cases like ID=3, the StartDate isn't equal, so it should remain with a Null and Non Null TerminationDate.

Resulting table would be:

ID	SystemDate	StartDate	TerminationDate
1	9/30/2017	1/1/2017	10/15/2017
1	4/1/2017	1/1/2017	12/31/2017
1	12/25/2016	1/1/2017	10/15/2017
2	1/15/2017	2/1/2017	5/31/2017
3	5/10/2015	6/1/2015	-
3	7/15/2016	7/21/2016	7/21/2017
4	7/20/2016	8/1/2016	12/31/2017
4	10/1/2017	8/1/2016	12/31/2017

Having a Null Date is fine; it should represent someone currently Employed.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    ID, SystemDate, StartDate, TerminationDate

    1, 9/30/2017, 1/1/2017, 10/15/2017

    1, 4/1/2017, 1/1/2017, 12/31/2017

    1, 12/25/2016, 1/1/2017,

    2, 1/15/2017, 2/1/2017, 5/31/2017

    3, 5/10/2015, 6/1/2015,

    3, 7/15/2016, 7/21/2016, 7/21/2017

    4, 7/20/2016, 8/1/2016,

    4, 10/1/2017, 8/1/2016, 12/31/2017

];

Left Join (Table)

LOAD ID,

StartDate,

FirstSortedValue(TerminationDate, -SystemDate) as NewTerminationDate

Resident Table

Group By ID, StartDate;

FinalTable:

LOAD ID,

StartDate,

SystemDate,

Alt(TerminationDate, NewTerminationDate) as TerminationDate

Resident Table;

DROP Table Table;

[shekhar_analyti]

Hi Sunny ,

please explain

Alt(TerminationDate, NewTerminationDate) as TerminationDate

[sunny_talwar]

Pick the first non-null value

[Anonymous]

Thanks Sunny. As always, you've been super helpful!