topic Re: Re: Fill Null Values with Previous Date in QlikView

Fill Null Values with Previous Date

Wed, 17 Sep 2014 15:17:17 GMT

Hello,

I need to fill the null values from field DATE_PROCESS with the last previous date selected in blue.

(The date format is DD/MM/YYYY)

I have the following table:

ACCOUNT	START_DT	END_DT	DATE_PROCESS
855000000	02/08/2013	05/09/2013	04/09/2013
855000000	05/09/2013	02/10/2013	15/09/2013
855000000	02/10/2013	04/11/2013	15/10/2013
855000000	04/11/2013	03/12/2013
855000000	03/12/2013	03/01/2014
855000000	03/01/2014	03/02/2014
855000000	03/02/2014	05/03/2014
855000000	05/03/2014	04/04/2014
855000000	04/04/2014	06/05/2014	23/04/2014
855000000	06/05/2014	04/06/2014	13/05/2014
855000000	04/06/2014	03/07/2014
855000000	03/07/2014	06/08/2014	21/07/2014
855000000	06/08/2014	03/09/2014	21/07/2014

(Where the blank values are null)

And I want to get something like this:

ACCOUNT	START_DT	END_DT	DATE_PROCESS
855000000	02/08/2013	05/09/2013	04/09/2013
855000000	05/09/2013	02/10/2013	15/09/2013
855000000	02/10/2013	04/11/2013	15/10/2013
855000000	04/11/2013	03/12/2013	15/10/2013
855000000	03/12/2013	03/01/2014	15/10/2013
855000000	03/01/2014	03/02/2014	15/10/2013
855000000	03/02/2014	05/03/2014	15/10/2013
855000000	05/03/2014	04/04/2014	15/10/2013
855000000	04/04/2014	06/05/2014	23/04/2014
855000000	06/05/2014	04/06/2014	13/05/2014
855000000	04/06/2014	03/07/2014	13/05/2014
855000000	03/07/2014	06/08/2014	21/07/2014
855000000	06/08/2014	03/09/2014	21/07/2014

Is there any way to do this?

Thank you!!!

Re: Fill Null Values with Previous Date

Wed, 17 Sep 2014 15:20:10 GMT

Hello David,

How is this table sorted by ?

Regards,

Kalpesh

Re: Fill Null Values with Previous Date

MarcoWedel — Wed, 17 Sep 2014 15:20:43 GMT

LOAD

other columns,

Alt(DATE_PROCESS, Peek(DATE_PROCESS)) as DATE_PROCESS,

FROM yourSource;

Re: Fill Null Values with Previous Date

Wed, 17 Sep 2014 15:25:56 GMT

Hi,

you can use peek function to represent that

isnull(peek(Dateprocess))as new process

Re: Fill Null Values with Previous Date

MK_QSL — Wed, 17 Sep 2014 15:30:36 GMT

Temp:

Load

ACCOUNT,

Date(START_DT) as START_DT,

Date(END_DT) as END_DT,

IF(IsNull(DATE_PROCESS) or LEN(TRIM(DATE_PROCESS))=0, Null(), DATE_PROCESS) as DATE_PROCESS

Inline

[

ACCOUNT, START_DT, END_DT, DATE_PROCESS

855000000, 02/08/2013, 05/09/2013, 04/09/2013

855000000, 05/09/2013, 02/10/2013, 15/09/2013

855000000, 02/10/2013, 04/11/2013, 15/10/2013

855000000, 04/11/2013, 03/12/2013,

855000000, 03/12/2013, 03/01/2014,

855000000, 03/01/2014, 03/02/2014,

855000000, 03/02/2014, 05/03/2014,

855000000, 05/03/2014, 04/04/2014,

855000000, 04/04/2014, 06/05/2014, 23/04/2014

855000000, 06/05/2014, 04/06/2014, 13/05/2014

855000000, 04/06/2014, 03/07/2014,

855000000, 03/07/2014, 06/08/2014, 21/07/2014

855000000, 06/08/2014, 03/09/2014, 21/07/2014

];

NoConcatenate

Final:

Load

ACCOUNT,

START_DT,

END_DT,

IF(IsNull(DATE_PROCESS),PEEK(DATE_PROCESS),DATE_PROCESS) as DATE_PROCESS

Resident Temp

Order By ACCOUNT, START_DT;

Drop Table Temp;

Re: Re: Fill Null Values with Previous Date

MarcoWedel — Wed, 17 Sep 2014 16:00:39 GMT

LOAD ACCOUNT,
    START_DT,
    END_DT,
    Alt(Date(DATE_PROCESS), Peek(DATE_PROCESS)) as DATE_PROCESS
FROM [http://community.qlik.com/thread/133786] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco