Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
misterkingsley
Creator
Creator

Rewrite Null Value to Correct Value

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:

IDSystemDateStartDateTerminationDate
19/30/20171/1/201710/15/2017
14/1/20171/1/201712/31/2017
112/25/20161/1/2017-
21/15/20172/1/20175/31/2017
35/10/20156/1/2015-
37/15/20167/21/20167/21/2017
47/20/20168/1/2016-
410/1/20178/1/201612/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:

IDSystemDateStartDateTerminationDate
19/30/20171/1/201710/15/2017
14/1/20171/1/201712/31/2017
112/25/20161/1/201710/15/2017
21/15/20172/1/20175/31/2017
35/10/20156/1/2015-
37/15/20167/21/20167/21/2017
47/20/20168/1/201612/31/2017
410/1/20178/1/201612/31/2017

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

Thanks in advance.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

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
Specialist
Specialist

Hi Sunny ,

please explain


Alt(TerminationDate, NewTerminationDate) as TerminationDate

sunny_talwar

Pick the first non-null value

misterkingsley
Creator
Creator
Author

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