Create a column with the last non null value

[gitguto]

Hello everybody,

I have a table like this:

TABLE:

SON	FATHER	GRANDFATHER
Little John	John	Big John
Little Peter	Peter
Little Bob
Little Mary	Mary	Big Mary

I need to create a column in the end of the table with the last value that is not null, it would look like this:

SON	FATHER	GRANDFATHER	LAST
Little John	John	Big John	Big John
Little Peter	Peter		Peter
Little Bob			Little Bob
Little Mary	Mary	Big Mary	Big Mary

I've tried two approaches unsuccesfully:

JOIN(TABLE)

LOAD

1) if(len(trim(GRANDFATHER))>0,GRANDFATHER,peek(GRANDFATHER)) as FINAL
2) if(IsNull(GRANDFATHER),FATHER,AVO) as FINAL

RESIDENT TABLE

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD * INLINE [
    SON, FATHER, GRANDFATHER
    Little John, John, Big John
    Little Peter, Peter,  
    Little Bob,  ,  
    Little Mary, Mary, Big Mary
];

Left Join(tab1)
LOAD SON, Pick(Match(-1, Len(GRANDFATHER)>0, Len(FATHER)>0, Len(SON)>0),
			GRANDFATHER, FATHER, SON) As LAST
Resident tab1;

[gitguto]

Thank you very much! Can you explain how does it works?

[gitguto]

I just realized it doesnt work if I have two sons or fathers with the same name. if this happens, it duplicates the lines.

SON	FATHER	GRANDFATHER
Little John	John	Big John
Little John	Johnny
Little Peter	Peter
Little Bob
Little Mary	Mary	Big Mary

It results in this:

SON	FATHER	GRANDFATHER	LAST
Little John	John	Big John	Big John
Little John	John		Johnny
Little John	Johnny		Big John
Little John	Johnny		Johnny
Little Peter	Peter		Peter
Little Bob			Little Bob
Little Mary	Mary	Big Mary	Big Mary

is there a solution in which I dont get duplicate rows? my desired output would be this:

SON	FATHER	GRANDFATHER	LAST
Little John	John	Big John	Big John
Little John	Johnny		Johnny
Little Peter	Peter		Peter
Little Bob			Little Bob
Little Mary	Mary	Big Mary	Big Mary

Thank you

[MayilVahanan · Accepted solution]

Hi

You can use the @Saravanan_Desingh  expression in ur main table itself. No need to join the tables.

LOAD *,Pick(Match(-1, Len(GRANDFATHER)>0, Len(FATHER)>0, Len(SON)>0),
GRANDFATHER, FATHER, SON) As LAST INLINE [
SON, FATHER, GRANDFATHER
Little John, John, Big John
Little Peter, Peter,
Little Bob, ,
Little John, Johnny,
Little Mary, Mary, Big Mary
];

Pick(Match(-1, Len(GRANDFATHER)>0, Len(FATHER)>0, Len(SON)>0),
GRANDFATHER, FATHER, SON)

<-- short form of multiple if statement 

if(Len(GRANDFATHER)>0, GRANDFATHER, If(Len(FATHER)>0, FATHER, SON)) as LAST

You can try like above also, both will give same result

[gitguto]

Thank you!