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

[adimiz123]

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	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

The table that I am trying to get...

CarNum	Date	Employee	Driver	Location
1234	26/07/2018	Avi	John	HAIFA

I need your help to find a solution,

Thank's Adiel

[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

[YoussefBelloum]

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 · Accepted solution]

Seems to work okay for me

[YoussefBelloum]

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]

Thanks