Leading zeros and Excel

Excel has a nasty habit of stripping out leading zeros from your data because when it sees something that looks like it might be a number – it wants to use it that way.

This can be a real problem if you are working with things like account ids, or US Postal codes.

When you are importing into QlikView – here is a simple trick you can use to force it to be interpreted as a string and not a number:

Tack a null string on to the beginning of the field.

So…

LOAD
numericfield1,
field2....

becomes


LOAD
'' & numericfield1 as numericfield1,
field2....

Nice and simple, or was that quick and dirty?

ws

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s