date set to 1700-01-01 - best way to conver to null

[brendafos]

I am new to Lawson data. 

Date fields to default to 1700-01-01.  I think it would be better to set these dates to null.

What would be the correct approach to this? 

The date columns do have some valid, non-null dates. 

[JohnJPS]

Would something like this work:

IIF(!IsEmpty([date] AND [date]=='1700-01-01',Null(),[date])

[MarqueeCrew · Accepted solution]

@brendafos,

Use a multi-field formula and select all date fields

IIF([_CurrentField_]=='1700-01-01',null(),[_CurrentField_])

This will set 1700 to NULL and keep all other dates.

Thanks & have a great weekend,

Mark

[brendafos]

Hum, did not work...

[MarqueeCrew]

@brendafos,

Glad that you updated for date/time.  The checkbox is set for creation of new data fields.  Did you check the new_field values?

[brendafos]

... I see your point about looking for new columns, which I didn't do

I tried it again, again no luck

[MarqueeCrew · Accepted solution]

How about incorporating left([field],4)=="1700" for the test?

[brendafos]

I changed it to double quotes around the date and then it worked.

Why?

[MarqueeCrew]

I'll let Alteryx answer that one. Thanks for the update.

[brendafos]

This has ended up being the best choice.

THANKS this is a huge fix.