How can we filter for null strings in the new data prep experience? We have a column that has null values in it and when we filter the data, we do not want any records that contain nulls. I have tried the following in the Comparison dropdown:
Does not equal
Does not contain
Is not in
With the following in the Value dropdown:
null
‘null’
blank
Hi @TraderRef,
Could you share a bit more about the field in question, is the value in the dataset actually listed as ‘null’ or something different? Have you tried ‘does not equal’ and then use ‘0’?
I just ran a basic test in my version and it looks to be handling accurately:
Initial dataset:
End Result:
So using ‘0’ in my filter logic actually filtered out the row that had ‘0’ and the row that had ‘null’.
Thank you for the quick response as I have a presentation today and am not having any success.
I picked a specific record that I know has nothing in the column and the value seems to show as a light gray value of null.
When I directly query the database for the record by saying SELECT * FROM table WHERE row_num = 1234, all columns are displayed for that record, but there is nothing in the column. When I query for that record and add the following:
WHERE primary_procedure _code = 0
WHERE primary_procedure _code = ‘ ‘
WHERE primary_procedure _code = ‘‘
the record is not returned.
However, when I change the query for that record to the following, it works:
WHERE primary_procedure _code IS NULL
There doesn’t seem to be a way to do the same in the Data Prep filter.
Hi @TraderRef,
As the new data prep experience is still in its’ early stages, there are some limitations and some features not operating as they did in the prior data prep experience. If this is an urgent matter, I would suggest switching back to the old legacy version to handle this for the time being.
@TraderRef Can you try primary_procedure_code is not in null? I just tested with a similar setup and it works correctly. Let us know if this still doesn’t work.
It doesn’t allow me to switch back to the legacy experience without rebuilding the entire dataset.
Again, I was originally looking for records where the primary_procedure_code has a value. Then I switched it up looking for records where the primary_procedure_code has nothing in it and the filter is not working. I am not sure if the data source matters, but it is Athena hitting data on S3. Maybe it would work if it were Redshift, or some other DBMS, but this is not working with what I have now.
@TraderRef Realize you want to show the blank/null values. Just tried adding an isNull() calculation and then a filter. Is this what you’re looking for?
That’s a terrific idea, @vignessh.b , thank you. I’m going to try it.
Hi @TraderRef,
Were you able test out the suggestion above and if so, did it work for your case?
I have not because the requirements changed and I found a different way to get the results I need, but I have this for reference if I need it for another set of requirements.
