Collibra Data Quality -Transformations - Replacing a string with NULL makes all the record values to NULL
Hi,
This is a pull DQ job.
We are trying to replace all the unwanted characters in a column to NULL string and then apply a NULL check to that column to catch all of them together.
But observing that though we have only a couple of unwanted characters, the replace transformations replaces all records to NULL for that column.
So tried to replace them with ''(empty) string. It works for one replace command, but with multiple replace transformations, observing that there is ambiguity error while applying empty check on this column.
Can someone please help?
Used the following codes for replace with NULL in a pullup job:
REPLACE(Email, '0', NULL) as Email
REPLACE(Email, '0', null) as Email
Also,
REPLACE(Email, '0', ' ') as Email
REPLACE(Email, 'D', ' ') as Email
REPLACE(Email, '~', ' ') as Email
The primary requirement is to have a single NULL/EMPTY check for all the NULL/Empty/~/D values using the tool NULL check.
ericgerstner
·1 year ago · EditedA couple of considerations...
Instead of chaining multiple
REPLACEfunctions, consider a more comprehensive approach usingCASEorREGEXP_REPLACE. This allows you to handle all unwanted characters in one step.---Or---
If you prefer using
REPLACE, normalize all unwanted characters to an empty string ('') and then convert these toNULLusing a secondaryCASEstatement.