Null() is not creating Null values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
 
Solved! Go to Solution.
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@slckkrky to get the NULL value use the below formula
IIF(isEmpty(Replace([_CurrentField_],'Null Value',Null())),Null(),[_CurrentField_])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Both solution works. thanks, but i still dont understand why my formula didnot work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
