topic Re: Create a column with the last non null value in App Development

Create a column with the last non null value

gitguto — Thu, 07 Oct 2021 00:28:47 GMT

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

Re: Create a column with the last non null value

Saravanan_Desingh — Thu, 07 Oct 2021 01:34:34 GMT

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;

Re: Create a column with the last non null value

gitguto — Thu, 07 Oct 2021 01:56:17 GMT

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

Re: Create a column with the last non null value

gitguto — Thu, 07 Oct 2021 02:13:30 GMT

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

Re: Create a column with the last non null value

MayilVahanan — Thu, 07 Oct 2021 02:27:58 GMT

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

Re: Create a column with the last non null value

gitguto — Thu, 07 Oct 2021 03:42:55 GMT

Thank you!