Null() is not creating Null values

[slckkrky]

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

 

[binuacs · Accepted solution]

@slckkrky to get the NULL value use the below formula

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

[IraWatt · Accepted solution]

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 solution]

@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

[slckkrky]

Both solution works. thanks, but i still dont understand why my formula didnot work.

[IraWatt]

@slckkrky no worries 😄 the formula did not work due to how Alteryx have implemented the Replace function. Arguably your formula should work but that's not how Alteryx wrote the replace function to work.