How do I get Automatic Lookup to handle null value lookups?

[khudson3]

My automatic lookup csv file is using say 2 columns; Col1 & Col2. Row entries are 'Success' & 'Failure' in Col1. Col 2 has the value / char '1' & null / no value entry in the opposite cells. I want searches to lookup and replace null with value=Failure. I'm seeing the mapping for 'Success' working but nothing for null / no cell value entry. Is there a csv file value / character entry that maps to null search value?

[woodcock]

You can add | fillnull value="Failure" after the lookup or you can create a lookup definition that sets a default value and use the lookup definition with the | lookup call (not the lookup file).

[HiroshiSatoh]

Isn't it possible to use calculated fields instead of "automatic lookup"?

[DavidHourani]

yeah that's a better idea if it's just success/failure 🙂

[khudson3]

The real life scenario is mapping to lists from a specification, where there will be more than just two outcomes:

Col1,Col2
No failure,null
Source application or not sent,0
System,1

[DavidHourani]

why not just use fillnull first and follow that up with a lookup ?

[khudson3]

Yes I could easily workaround the issue with fullnull or eval etc. But I wanted to understand whether there was a direct csv mapping that could work, as it seems quite poor if automatic lookup cannot handle null values appropriately.

[richgalloway]

The easiest thing to do is change the lookup csv file to not have an empty Col2.

[khudson3]

Null is being returned for the search that the lookup is running on, so 0 cannot be used and the fullnull command can only be executed after the search. Need to know whether there is a csv mapping for null char.

[richgalloway]

Have you tried using isnull to test the output of the lookup? Share your query and I may be able to be more specific?

[khudson3]

Yes I've tried issull. My search events are returning true null values, but the auto lookup is not applying / handling the transformation and the values remain null. In the auto lookup csv I've used entries ="",empty etc.

If I use | fillnull value=- or equiv then I see the null automatic lookup new fields but the data is still null.

The search query does not really have a bearing on the question as it contains no transformations.

[khudson3]

Doesn't have an empty Col2 it has 1 & null cells. 1 is working for Success whereas null is not being mapped.

[khudson3]

Col1 Col2
Success 1
Failure

[richgalloway]

null==empty. If you can change the CSV file to have

Col1,Col2
Success,1
Failure,0

then you'll avoid convoluted SPL.