How to identify smallest byte size needed to represent a column without dataloss?

[Joshman108]

Title says it all.

[apathetichell]

https://help.alteryx.com/current/designer/auto-field-tool

[Joshman108]

I don't see how autofield does this.

It does not change the byte size in a downstream select nor does it give you a message box that i see

[apathetichell · Accepted solution]

compare your string values going in and coming out via  a select tool.

Autofield's description is literally that it does what you asked for. I do not believe it converts numbers directly to bytes but ints can be converted to bytes with a select tool and other numbers must  be first turned into ints and then bytes. 

from tool description:

Use Auto Field to read through all of the records of an input and set the field type to the smallest possible size relative to the data contained within the column.

The tool correctly assigns a numeric field to a string data type where any record starts with zero and not a number.

[Joshman108]

ok i retract my previous statement, it did not seem to have been working but now it is.

thanks

[apathetichell]

With number it's not really clear - but the string difference is huge (and noticeable with selects before and after). There is a potential way to use a macro to automate turning an integer into a bye that I can test out if you want.

[Joshman108]

Sure, that would be good. The reason I'm asking is I'm trying to understand what data sizes I need for an outgoing table I'm creating

[danilang]

Hi @Joshman108 , @apathetichell 

In a table with numerous fields, getting the correct field size can save a significant amount of space.  However, the Autofield tool works well at compacting String data types, but does nothing at all for numeric types.  In the attached sample I started with this sample data, where the field names are the smallest size necessary to hold all the values in the column 

I followed this with a Select tool to force all columns to their maximum sizes, double for float and double, int64 for int and byte fields, decimal 50.10 for the decimal field.  Here are the field types after passing through the Autofield tool

As you can see, the string field was correctly sized, but the numeric fields were untouched.   

To truly get the minimal size for each of your fields, run your data through the Field Summary tool.  This will give you the min and max values for each numeric field.  Based on these min and max values, you can correctly set the smallest field type in your database using the documentation about its available field types and their min and max values.

Dan