My "stats latest" search is inserting values from other fields when the value is actually NULL. How should I edit my search?

[kamal_jagga]

Hi,

We have been using the stats latest(field) for quite sometime and it worked quite well. But for a new file, sometimes few fields are empty. Stats latest is mixing up the data from 2 rows and is giving the latest not null value of that field.

Test Data:

Emp   Name   Company  Address   _time
1     A      XYZ      Phoenix   Jan 1, 2017
2     B      PQR      Seattle   Feb 1, 2017
3     A      PQR                Feb 1, 2017

Search:

stats latest(company) as company latest(Address) as Address by Name

Result:

Name   Company  Address 
B      PQR      Seattle   
A      PQR      Phoenix   

The address for A is showing as "Phoenix" even though there was no address mentioned. Its picking up value of address from previous row of A.

Looking for a parameter which gives me the latest value even if it was NULL. (Don't want to use fillnull before stats)

Expected Result:

Name   Company  Address 
B      PQR      Seattle   
A      PQR                           

Kindly advise.

[lguinn2 · Accepted solution]

The latest function always returns the latest non-null value. There is no way to make the function return a null value.
So yes, you will need to fill null before the stats...

[kamal_jagga]

Okay. Thanks