How to show null or empty feilds produced by a lookup table

[jravida]

Hi folks,

I'm doing a lookup table (on some data that would take too much time to explain without more confusion), invoked by a stats command.
For simplicity sake let's say it is food items. I would have a lookup table for every item in the store, but only want to see that which has no UPC associated. SO I would run:
... | lookup food2upc.csv itemName | stats list(upcCode) by itemName

I would get a list of all the items, and would see a blank/whitespace for the cells/value where there is no UPC in the database. This query works fine.
I want to further filter the list to only show the items with no UPC, filtering out the ones that do. I've tried many combinations of where,search and can't get any results. Among searches I've tried:
... | lookup food2upc.csv itemName | stats list(upcCode) by itemName | fillnull=blank |where upcCode=blank

---still nothing comes up in the search. Can someone assist?

[stephane_cyrill · Accepted solution]

... | lookup food2upc.csv itemName | stats list(upcCode) by itemName | fillnull value=blank |where upcCode=blank

[maciep]

I'm not sure what else you have you tried, but maybe put pipe to fillnull before your stats. Also in your example, isn't the field called list(upcCode) not upcCode after your stats? Meaning, the upcCode field in the where clause doesn't exist anymore at that point?

[stephane_cyrill]

Hi ,
I can see that you have misuse your fillnull. try to use

fillnull value=blank

isntead of

fillnull=blank