topic Rewrite Null Value to Correct Value in QlikView

Rewrite Null Value to Correct Value

Anonymous — Thu, 11 Jan 2018 14:09:10 GMT

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.

Re: Rewrite Null Value to Correct Value

sunny_talwar — Thu, 11 Jan 2018 14:22:44 GMT

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;

Re: Rewrite Null Value to Correct Value

shekhar_analyti — Thu, 11 Jan 2018 15:01:10 GMT

Hi Sunny ,

please explain

Alt(TerminationDate, NewTerminationDate) as TerminationDate

Re: Rewrite Null Value to Correct Value

sunny_talwar — Thu, 11 Jan 2018 15:11:37 GMT

Pick the first non-null value

Re: Rewrite Null Value to Correct Value

Anonymous — Thu, 11 Jan 2018 15:36:30 GMT

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