Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gitguto
Contributor III
Contributor III

Create a column with the last non null value

Hello everybody,

I have a table like this:

TABLE:

SONFATHERGRANDFATHER
Little JohnJohnBig John
Little PeterPeter 
Little Bob  
Little MaryMaryBig 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:

SONFATHERGRANDFATHERLAST 
Little JohnJohnBig JohnBig John
Little PeterPeter Peter
Little Bob  Little Bob
Little MaryMaryBig MaryBig 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

2 Solutions

Accepted Solutions
Saravanan_Desingh

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;

commQV11.PNG

View solution in original post

MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

5 Replies
Saravanan_Desingh

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;

commQV11.PNG

gitguto
Contributor III
Contributor III
Author

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

gitguto
Contributor III
Contributor III
Author

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

SONFATHERGRANDFATHER
Little JohnJohnBig John
Little JohnJohnny 
Little PeterPeter 
Little Bob  
Little MaryMaryBig Mary

 

It results in this:

SONFATHERGRANDFATHERLAST
Little JohnJohnBig JohnBig John
Little JohnJohn Johnny
Little JohnJohnny Big John
Little JohnJohnny Johnny
Little PeterPeter Peter
Little Bob  Little Bob
Little MaryMaryBig MaryBig Mary

 

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

SONFATHERGRANDFATHERLAST
Little JohnJohnBig JohnBig John
Little JohnJohnny Johnny
Little PeterPeter Peter
Little Bob  Little Bob
Little MaryMaryBig MaryBig Mary

 

Thank you

MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R
gitguto
Contributor III
Contributor III
Author

Thank you!