cleansing data tool

[Xiaorui]

Hi Community,

I have a table some are nulls and some are 0, I want to place null data with a dash - , as if replace the null with 0 will confuse with the original 0 data, but dash - is not numerical.

A table like this, fill blanks with dash and keep the 0.

[ShankerV]

Hi @Xiaorui 

You can overcome the issue with the Multi field formula.

Like below is the example.

Many thanks

Shanker V

[ShankerV]

Hi @Xiaorui 

I have mocked the data's to show how the output works.

Output:

Input:

Feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

Many thanks

Shanker V

[binuacs]

@Xiaorui One way of doing this with the Multi-Field formula, the problem is all your data will be changed to string type

[Xiaorui]

Hi Shanker,

Thanks for your answer, the thing is in this way the numerical data would change to Strings, but I need to keep them as numerical.

[JamesCharnley]

If the field needs to stay numerical then there are no workarounds to have dashes in afaik. It needs to be a string. You'd have to do this as the final step if the dashes are purely for the sake of formatting, or you'll need to leave them null if zero isn't an option.

[ShankerV]

Hi @Xiaorui 

Numerical columns cannot contain any other characters except digits.

It can be null or empty or 0.

Many thanks

Shanker V

[Xiaorui]

Hi James,

thanks for your answer.

Yes, it's just a formatting thing. When you say final step do you mean add dash manually or there is a way to add it using alteryx?

[JamesCharnley]

Hi @Xiaorui 

I'm making some assumptions basically that you need it to stay numerical because you're applying calculations somewhere afterwards. If you were only adding the dashes as a formatting step to make it look nice to present the table as such, this could just be your final step.

What's the reason you need the field to be numerical?