The ASCII function in Tableau returns the ASCII code of the first character of a string. While its use might seem limited, it can be quite useful in many scenarios, especially when dealing with text data.
Example: ASCII([Product Name])
This field will store the ASCII value of the first character of each product name.
Here are some use cases where the ASCII function can be effectively utilized in Tableau.
1. Data Cleaning and Validation
- Identify Non-Printable Characters
The ASCII function can help identify non-printable characters or special characters in a dataset. Each character on a computer — printable and non-printable — has a number known as its Unicode character code or value. Another, older, and better-known character set is ASCII, which stands for the American Standard Code for Information Interchange, has been incorporated into the Unicode set
As a result, the first 32 characters (0 to 31) of the Unicode and ASCII sets are identical. They are used by programs to control peripheral devices such as printers, across different platforms. As such, they are not intended for use in a worksheet and can cause errors when present.
IF ASCII([Your Field]) < 32 OR ASCII([Your Field]) > 126 THEN “Non-printable” ELSE “Printable” END
- Filtering Out Special Characters
By using the ASCII function in combination with conditional statements, you can filter out special characters from your dataset. This is particularly useful for cleaning data inputs.
Suppose you want to identify Your Field whose names start with non-alphabetic characters:
IF ASCII([Your Field]) < 65 OR (ASCII([Your Field]) > 90 AND ASCII([Your Field]) < 97) OR ASCII([Your Field]) > 122 THEN “Non-alphabetic” ELSE “Alphabetic” END
- Data Validation: By converting characters to their ASCII values, you can validate input data against expected ASCII ranges. For example, ensuring that a field contains only numeric values, letters, or specific symbols.
IF ASCII([Field]) >= 48 AND ASCII([Field]) <= 57 THEN [Field] ELSE ‘Invalid’ END
2. Categorizing Data
- Character Classification: You can use the ASCII function to classify characters into different categories, such as uppercase/lowercase digits or special characters. This can be helpful for text analysis and categorization tasks.
IF ASCII([Your Field]) >= 65 AND ASCII([Your Field]) <= 90
THEN ‘Uppercase Letter’
ELSEIF ASCII([Your Field]) >= 97 AND ASCII([Your Field]) <= 122
THEN ‘Lowercase Letter’
ELSEIF ASCII([Your Field]) >= 48 AND ASCII([Your Field]) <= 57
THEN ‘Digit’ ELSEIF (ASCII([Your Field]) >= 32 AND ASCII([Your Field]) <= 47)
OR (ASCII([Your Field]) >= 58 AND ASCII([Your Field]) <= 64)
OR (ASCII([Your Field]) >= 91 AND ASCII([Your Field]) <= 96)
OR (ASCII([Your Field]) >= 123 AND ASCII([Your Field]) <= 126)
THEN ‘Punctuation’
ELSE ‘Other’
END
3. Sorting and Ordering
- Custom Alphabetical Sorting: The ASCII function can be used to create custom sorting orders. For example, if you want to sort data based on the ASCII values of the first character of a string, this function can be instrumental.
INT(STR(ASCII(UPPER(MID([Your Field],1))))+
STR(ASCII(UPPER(MID([Your Field],2))))+
STR(ASCII(UPPER(MID([Your Field],3))))+
STR(ASCII(UPPER(MID([Your Field,4))))+
STR(ASCII(UPPER(MID([Your Field],5)))))…..
RANK_UNIQUE(AVG(ASCII([Your Field])))
4. Creating Unique Keys
- Unique keys: By combining the ASCII values of characters in a string, you can create a simple hash function to generate unique identifiers or keys for your data entries.
ASCII(LEFT([Your Field], 1)) + ASCII(MID([Your Field], 2, 1)) + ASCII(MID([Your Field], 3, 1))
5. Frequency Analysis
- Analyze the frequency of specific characters or types of characters (e.g., digits, letters) within your dataset.
To count the number of times a specific character (e.g., ‘A’) appears as the first character in a dataset:
SUM(IF ASCII([Your Field]) = ASCII(‘A’) THEN 1 ELSE 0 END)
Let’s check Avery Non-Stick Binders — 20
By leveraging the ASCII function in Tableau, you can enhance your data processing capabilities, ensure data quality, and perform advanced text manipulations, making it a powerful tool in your data analysis toolkit.
I hope you like using ASCII functions and see the advantage of it.
