How to replace a blank cell with "0"

[gagandeep_dhall]

Hi Everyone,

How can I replace a blank cell in a column by "0".

Status	Total number
completed	2
pending	1
error 1
error 2	1

What is happening is:

Date	Completed	pending	error 1	error 2
Jan 7	2	1	null	1

[ponraj]

Try Data cleansing tool.  it replaces nulls with blanks for string fields and nulls with zero for numeric fields. 

[CharlieS]

Hi @gagandeep_dhall 

I suggest checking out the Data Cleansing tool. One of the functions of that tool is to replace null numeric field with 0.

https://help.alteryx.com/current/designer/data-cleansing-tool 

An important note is that this requires the field to be a numeric type (Int, Double, etc). If it not, you could either convert the field to numeric, or add the string character "0". There's lots of ways to do either so let us know if we can help with that. 

[gagandeep_dhall]

Hi @CharlieS 

thanks a ton. 

I used it and now it no longer shows me null.

But I figured out that I have to actually put "1" in all the blank fields for it to count that field 🙂

How can I do that?

Regards,

Gagandeep

[gagandeep_dhall]

@CharlieS  to add to the above query, I tried using replace function, but it gives me an error that "tried to apply string operator to numeric value"

[gagandeep_dhall]

Hi @CharlieS  I was able to do that 🙂 

Do you know how can I add "%" to a set of values in columns?

[CharlieS]

@gagandeep_dhall wrote:

But I figured out that I have to actually put "1" in all the blank fields for it to count that field 🙂

---

---

@gagandeep_dhall wrote:

I tried using replace function, but it gives me an error that "tried to apply string operator to numeric value"

---

One way to achieve that is to apply a formula expression to that field that uses a simple IF statement like this:

IF IsEmpty([Total Number]) THEN 1
ELSE [Total Number] ENDIF

---

@gagandeep_dhall wrote:

Do you know how can I add "%" to a set of values in columns?

---

---

Again, there's a few ways to go about this.

1. Convert the field to a string so the "%" character can be added with an expression like: [Total Number]+"%"

2. If you plan to output to a file like Excel, then you could use the Table tool in the Reporting section to create a layout which include a "%" suffix on the field before it is rendered to Excel. 

Examples of all attached.