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?
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
).
Isn't it possible to use calculated fields instead of "automatic lookup"?
yeah that's a better idea if it's just success/failure 🙂
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
why not just use fillnull
first and follow that up with a lookup ?
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.
The easiest thing to do is change the lookup csv file to not have an empty Col2.
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.
Have you tried using isnull
to test the output of the lookup? Share your query and I may be able to be more specific?
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.
Doesn't have an empty Col2 it has 1 & null cells. 1 is working for Success whereas null is not being mapped.
Col1 Col2
Success 1
Failure
null==empty. If you can change the CSV file to have
Col1,Col2
Success,1
Failure,0
then you'll avoid convoluted SPL.