Fill Null Values with Previous Date

[#1]

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

[#2]

Hello David,

How is this table sorted by ?

Regards,

Kalpesh

[MarcoWedel]

LOAD

other columns,

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

FROM yourSource;

[#4]

Hi,

you can use peek function to represent that

isnull(peek(Dateprocess))as new process

[MK_QSL]

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;

[MarcoWedel]

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