topic Re: How to get the Last Value But not if it Null ? in QlikView

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

Xabinav — Thu, 26 Jul 2018 14:28:31 GMT

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

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

sunny_talwar — Thu, 26 Jul 2018 14:45:05 GMT

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

];

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

Anil_Babu_Samineni — Thu, 26 Jul 2018 14:47:55 GMT

Try this way?

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

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

YoussefBelloum — Thu, 26 Jul 2018 15:23:38 GMT

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

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

sunny_talwar — Thu, 26 Jul 2018 15:37:32 GMT

Seems to work okay for me

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

YoussefBelloum — Thu, 26 Jul 2018 15:42:55 GMT

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

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

sunny_talwar — Thu, 26 Jul 2018 15:43:44 GMT

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

Xabinav — Sun, 29 Jul 2018 07:49:17 GMT

Thanks