How to take the first value that is not null above the current Null value in a column.

[cheburashka]

Hello,

I have a table:

MID	IHD_TYPE	STAD	INT
08 - telefoongesprek	25 - actiecode contactcenter	12	1 - initiatief cliënt
08 - telefoongesprek	25 - actiecode contactcenter		2 - initiatief vennootschap
08 - telefoongesprek	25 - actiecode contactcenter		2 - initiatief vennootschap
08 - telefoongesprek	25 - actiecode contactcenter		2 - initiatief vennootschap

On the 2nd, 3rd, 4th row I also want the value 12. What expression should I write in my load to fill in my column?

Note that my real table is larger than this one and I have multiple open Nulls and values in STAD.

Meta code:

(check if IsNull(STAD)=0 then use STAD while moving up row by row)  as STAD

,Koen

[sunny_talwar · Accepted solution]

May be like this:

If(IsNull(STAD), Peek('STAD'), STAD) as STAD

[MK_QSL]

Something like below. Haven't tested..

Temp:

Load MID, IHD_TYPE, STAD, INT From ....

Data:

Load MID, IHD_TYPE, If(Not IsNull(STAD), STAD, If(MID & IHD_TYPE = PREVIOUS(MID & IHD_TYPE), Previous(STAD))) as STAD

Resident Temp

Order By MID, IHD_TYPE,STAD;

Drop Table Temp;

[bkn]

TEST1:

LOAD * INLINE [

    MID, IHD_TYPE, STAD, INT

    08 - telefoongesprek, 25 - actiecode contactcenter, 12, 1 - initiatief cliënt

    08 - telefoongesprek, 25 - actiecode contactcenter, , 2 - initiatief vennootschap

    08 - telefoongesprek, 25 - actiecode contactcenter, , 2 - initiatief vennootschap

    08 - telefoongesprek, 25 - actiecode contactcenter, , 2 - initiatief vennootschap

];

NoConcatenate

TEST2:

LOAD

  MID, IHD_TYPE, INT, If(STAD='',Peek('STAD'),STAD) AS STAD

Resident TEST1;

DROP Table TEST1;