Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
adimiz123
Contributor III
Contributor III

How to get the Last Value But not if it Null ?

Hello everyone,

I have a table with the following data, I am looking for a way to take the last value for every date that is NOT NULL

For example - I tried to do it like :

LOAD

CarNum, Date, LastValue(Employee) as Employee..   But I get Null on Employee field.

Group By CarNum, Date;

CarNum

Line

DateEmployeeDriverLocation
1234126/07/2018David-TLV
1234226/07/2018AviBorisNYU
1234326/07/2018-Asaf-
1234426/07/2018-John-
1234526/07/2018--HAIFA

The table that I am trying to get...

CarNumDateEmployeeDriverLocation
123426/07/2018AviJohnHAIFA

I need your help to find a solution,

Thank's Adiel

1 Solution

Accepted Solutions
sunny_talwar

Seems to work okay for me

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

Try this

Table:

LOAD CarNum,

Date,

FirstSortedValue(If(Len(Trim(Employee)) > 0, Employee), -If(Len(Trim(Employee)) > 0, Line)) as Employee,

FirstSortedValue(If(Len(Trim(Driver)) > 0, Driver), -If(Len(Trim(Driver)) > 0, Line)) as Driver,

FirstSortedValue(If(Len(Trim(Location)) > 0, Location), -If(Len(Trim(Location)) > 0, Line)) as Location

Group By CarNum, Date;

LOAD * INLINE [

    CarNum, Line, Date, Employee, Driver, Location

    1234, 1, 26/07/2018, David, , TLV

    1234, 2, 26/07/2018, Avi, Boris, NYU

    1234, 3, 26/07/2018, , Asaf

    1234, 4, 26/07/2018, , John

    1234, 5, 26/07/2018, , , HAIFA

];

Anil_Babu_Samineni

Try this way?

FirstSortedValue(If(Len(Trim(Employee)) > 0, Employee), -If(Len(Trim(Employee)) > 0, CarNum)) as Employee

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
YoussefBelloum
Champion
Champion

Hi stalwar1‌,

do you think it will work if we take the exact script function and put it as expression on a straight table ? I'm finding difficulties doing it on the front-end..

sunny_talwar

Seems to work okay for me

Capture.PNG

YoussefBelloum
Champion
Champion

I'm trying to get it for a hour now.. loading OP's web table with '-' values and testing with len(trim())>0... -_-

Thank you

sunny_talwar

adimiz123
Contributor III
Contributor III
Author

Thanks