Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Seems to work okay for me

Capture.PNG

View solution in original post

7 Replies
Highlighted

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

];

Highlighted

Try this way?

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

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)
Highlighted
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..

Highlighted

Seems to work okay for me

Capture.PNG

View solution in original post

Highlighted
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

Highlighted

Highlighted
Contributor III
Contributor III

Thanks