Replace a specific date with nothing

I have a formula that looks for a specific date and currently replaces it with null, which works fine. I prefer not seeing [Null] when I browse the data. Data Cleansing tool does not remove the null.

IIf([Pickup Date]=todate("1940-01-01"),null(),[Pickup Date])

Thanks,

stapuff106

Date Time

Accepted answers

Thableaus

Hi @stapuff106

You can change your date to a string type

and use the Data Cleansing tool to replace Nulls with blanks.

But I don't think it's possible to have a Date Field with a blank.

So what I recommend is to do all your date calculations first, and add this step just before visualizing your data.

Cheers,

All comments

Thableaus

Hi @stapuff106

You can change your date to a string type

and use the Data Cleansing tool to replace Nulls with blanks.

But I don't think it's possible to have a Date Field with a blank.

So what I recommend is to do all your date calculations first, and add this step just before visualizing your data.

Cheers,

BenMoss

@Thableaus is spot on with his response; date and datetime fields do not accept empty strings "" as a valid option, so they would appear as NULL().

This is a common database thing. Personally I wouldn't follow the steps above to mask the values by converting them to strings as this can cause issues when loading them into other tools where the datatype is therefor not picked up correctly.

If you are writing the data to excel at the end a null() appears empty anyway, it is not value that is written.

Ben

Thableaus

@BenMoss well, actually I didn't take this step (loading data to another data repository) into consideration.

I mean, if he's just simply going to browse his data, I don't think turning it to a string is actually an issue.

Cheers,

stapuff106

@Thableaus I figured since the Data Cleansing tool did not have the option that Date Fields couldn't be blank, but I thought I would ask anyways.

I appreciate your response back.

stapuff106

Thanks @BenMoss for your response.

stapuff106

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335