Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Xabinav
Creator
Creator

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
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

Xabinav
Creator
Creator
Author

Thanks