edit: oops. listen to shawn.
Dylan is correct, his formula is the equivalent to your Excel formula. But you didn't account for the possibility of a 4-digit zip with extra text, or a 4-digit ZIP-plus (8-digit ZIP). Here's the calc to handle those:
IF ISNULL(INT(LEFT([Zipcode] + '00000',5)))
THEN LEFT([Zipcode] + '00000',4)
ELSE LEFT([Zipcode] + '00000',5)
EDIT: I just notice that isn't the equivalent. With 4-digit ZIPs it adds a number to the end, not the beginning as your Excel formula does. Standby.
Message was edited by: Shawn Wallwork
This turned out to be an interesting question. A couple of notes and then the solution.
- Official ZIP Codes can have anywhere from 3 to 10 digits (including the "-")
- Tableau recognizes ZIPs as ZIPs with and without the leading zeros
- Tableau interpretes INT(1234-) as a number -1234 (interesting wrinkle)
So then here's a formula to clean up 'dirty' ZIPs (as long as they start with the ZIP):
IF CONTAINS([Zipcode],"-" )
Clean Up ZIPs.twbx.zip 35.4 KB
Greetings everyone. When I apply Shawn's formula to the data interpreter preview in Tableau 10.1 and try to create a calculated field, this is what I get:
I apologize for not being able to show the original file since it is sensitive data. If there is anything that you can do to help me resolve this enigma that would be awesome.