Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Avoid Truncation

Community Data Engineer
Community Data Engineer
Created

Truncated data is usually defined as numeric rounding or cut off, string shortening, or datum deletion - essentially any time information is lost. Since, as analysts, our insights are only as good as our data, we usually find ourselves trying to preserve the integrity of data as we’re processing it. That’s not to say we can’t also optimize our resources usage when our data will allow for it.

 

While keeping your data types as small as possible is important, and can serve to shorten run times, it is even more important to understand what data types are most accommodating to your data and in what situations they can be shortened without truncation. If you’ve ever seen Office Space, you understand just how important even fractions of a cent are – and accuracy doesn’t just apply to financial data.

 

To avoid truncation in your data you have to first explore your data types in the context of your analyses. What data types and sizes did you receive the data in and why? What format would we like to see our results in? Are there opportunities to reduce memory usage between those two? And finally - what operations will we perform on the fields in our workflows that may impact each data type and size? The answers to these questions will be unique to each dataset, but once they’re addressed you can use the same techniques to keep your data both optimized and accurate.

 

Start by identifying the data types that most closely fits your fields based on the questions above. If you want Alteryx’s best guess, try using the Auto Field Tool to assign optimized data types and sizes automatically. While this tool is immensely helpful, be sure to check that the output is not truncating data or leaving it in a form less conducive to your downstream analyses – the Auto Field Tool doesn’t know the answers to your questions above. You can have the best of both worlds by adjusting the assignments from the Auto Field Tool, where necessary, by placing a Select Tool (master it here) just afterwards. You’ll then have suggestions and be able to change the less accurate/accommodating assignments by hand. Some things to consider:

 

  • If performing string operations later in your workflow that may increase their length, pay close attention to see if they are being truncated after that maximum string value is reached. String and WString (accepts Unicode characters) types are set length and will drop any characters that exceed their size. On the other hand, V_string and V_WString (accepts Unicode characters) are of variable length, and will adjust to accommodate strings after assignment.

 

  • Numerics may seem the most intuitive of the bunch, but pay close, close attention to the precision of each type so as to avoid unintentional rounding. Fixed Decimal is the only type to have an adjustable length – the rest may force your data to fit if not assigned to the correct type.

 

  • Dates are not always input in the standard ISO format yyyy-mm-dd HH:MM:SS, and may require some converting in order to handle them as Date/Time types in the Designer (this is important when trying to sort or filter by date or apply Date/Time functions in formulas). Any other date format will be interpreted as a string, and treated as such, unless converted using the DateTimeParse() function, the DateTime Tool, the Parse Dates Macro, or the fan favorite BB Date Macro. If you need to return to your original formatting, or simply prefer another, you can always convert your dates back into another format string after they’ve been processed using the DateTimeFormat() function or the DateTime Tool.
Comments
5 - Atom

I have an issue of truncating in formulas.  I'm creating a formula on a string using the directory input tool and the result is being truncated.  I have tried several different data types and the auto field macro as well with no success.

 

Do you have a suggestion for overcoming this?

Community Data Engineer
Community Data Engineer

Hey @Brad_Slaughter!

 

Is the formula using the same field that's output from the Directory Input Tool? If you create a new field for the resultant string you can adjust the size in the Formula Tool, or you can try placing a Select Tool after the Directory Input Tool to increase the field size prior to applying your Formula Tool logic.

 

Hopefully that helps accommodate your string!

 

Best,

MattD

8 - Asteroid

Hello-

I have been having troubles with the "Table" and "Render" tool while creating a Report . In the past i had just connected the Table Tool and Render Tool to out a report in XLSX file however since lately i don't see the same format as i see in the "Browse" tool. For example in the below workflow i can see perfectly formatted report on the left side under Browse tool but when i open the report which gets exported out to my local drive it looks like the snippet below where the column headers are truncated instead of WK_1 it shows W1 and also the column width are pretty small.I already checked the sizes of the data types. Can you please suggest what am i doing wrong ? Thank you.

 

 Capture.PNGCapture1.PNG

5 - Atom

Just kidding.... figured out my issue. 

 

8 - Asteroid

Doesn't help my issue.  Any thoughts?

6 - Meteoroid

@Mark_Lurie:

 

Not sure if you're using the Auto Field tool anywhere in your workflow but this is what was causing my issues with truncation. The V_WString data type was getting reassigned a size to much lower even after I would change it to different data types. 

8 - Asteroid

@Adrian01 

 

I tried with and without to Auto Field tool and neither worked.

6 - Meteoroid

Have you tried changing your data sizes manually with a select tool? When I used the Auto Field Tool this changed the size to one of my columns to '20' and I could only get rid of the truncation after changing it to 255.

8 - Asteroid

Tried that, too.  

What I find disturbing is that when I merged 15 200 character length fields and set the data type to V WString at the max value it still truncated the column.

6 - Meteoroid

@Mark_Lurie 

 

Without looking at your workflow, it's somewhat difficult to diagnose but what you describe sounds very strange. I found some documentation about the size limit and unless your fields are larger than 2.14 billion characters this should work.

 

Here's the link: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/String-size-limitations/td-p/45283

 

They mention some work-arounds that you might look at if they're applicable if they field is truly that large.

8 - Asteroid