Derived Column Transformation Editor - How to replace NULL character?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

serge - 03 Jul 2008 04:48 GMT

I am trying to import a text file which exists a column that has some NULL
values.
I am unable to import the data and have this NULL not report an error.

I tried writing the following expression in the Derived Column
Transformation Editor:
REPLACE(myColumn, HEX(00),"")

Is there a way to import a NULL value and not get any errors?

Thank you

Reply to this Message

jhofmeyr@googlemail.com - 03 Jul 2008 11:45 GMT

Hi Serge,

Try: IsNull(myColumn) ? "" : myColumn

Good luck!
J

Reply to this Message

Joe H - 03 Jul 2008 16:31 GMT

Here is a snip of ine of mine - if ISNULL replace with "N/A" else use
ProgramIndexDescription

ISNULL(ProgramIndexDescription) ? "N/A" : ProgramIndexDescription

>I am trying to import a text file which exists a column that has some NULL
>values.
[quoted text clipped - 7 lines]
>
> Thank you

Reply to this Message

jhofmeyr@googlemail.com - 07 Jul 2008 14:12 GMT

<quote>
... replace with "N/A" ...
</quote>

Different strokes for different folks ;)

I usually use empty string ("") because if the destination is a
delimited flat file, most systems that read the file will interpret
empty-string as a NULL. If the destination is a database, I'll
usually leave the column as NULL in the first place and leave the
defaulting to the database (create constraint on the column).

It all depends on your data integrity requirements. "N/A" (and indeed
"") actually mean something, whereas NULL means the value does not
exist ...

Good luck!
J

Reply to this Message

Joe H - 07 Jul 2008 16:57 GMT

My target table is a Datat Warehouse table. All columns have to have a
bucket to go into so the counts work correctly. Blank or null values go
into the "N/A" bucket.

> <quote>
> ... replace with "N/A" ...
[quoted text clipped - 14 lines]
> Good luck!
> J

Reply to this Message

jhofmeyr@googlemail.com - 07 Jul 2008 19:10 GMT

Yeah, defaulting is pretty standard in data warehousing, but you still
need to be careful how it is done ... blank != null != N/A (although
to be fair many businesses count them as the same for practical
purposes) It's the same concept as defaulting null numbers to 0 -
this is risky as it throws out any aggregation based on a count of
values (avg's, mean's, median's, etc)

Defaulting in the database means that you don't have to worry about
ensuring consistent defaulting in each of your ETL processes.

Just my opinion ;)
J

Reply to this Message