Null() is not creating Null values

Question

Hello everyone

I am trying to replace textual "Null Value" from all text files and using Multi Column Formula tool. Replace([_CurrentField_], "Null Value", null())

However results on new columns donot appear as an actual Null but appears as an empty cell.

When i use Null() function in a single Formula tool, it is working properly. Any idea how to solve this?

Thank you in advance

binu_acs · Accepted Answer

@slckkrky to get the NULL value use the below formula

IIF(isEmpty(Replace([_CurrentField_],'Null Value',Null())),Null(),[_CurrentField_])

IraWatt · Accepted Answer

Hey @slckkrky,

I think this might be a limitation of the Replace function. the function always return a string so when you pass NULL() as a second parameter it returns a reference to a string with nothing in it which is the same as an empty string record.

This approach works though:

IF [_CurrentField_] = "Null Value" THEN null() ELSE [_CurrentField_] ENDIF

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

grazitti_sapna · Accepted Answer

@slckkrky,

We cannot replace the data with NULL(), however there is another way of doing it though, you can replace the data by empty quotes and then use if to replace it with Null()

IF IsEmpty(Replace([_CurrentField_], "Null Value","")) THEN null() ELSE [_CurrentField_] ENDIF