Skip to main content

​
Intro

This article describes how to troubleshoot the following common errors when using Beast Mode calculations:

​
Case Statement Formatting

A case statement requires a few keywords placed in a particular order. 
    CASE
    WHEN `State` = 'California'
    THEN 'CA'
    ELSE `State`
    END
The case statement will be in error if it doesn’t include these elements or is not written correctly. You can include as many WHEN conditions as you would like, but must have a THEN for every WHEN. 
    CASE
    WHEN `State` = 'California'
    THEN 'CA'
    WHEN `State` = 'Washington'
    THEN 'WA'
    ELSE `State`
    END

​
Date Formatting

The functions you can use are DATE_FORMAT() and STR_TO_DATE(). When using the `STR _TO_DATE()` function you must use the exact same format your string column is already in so that the function can properly convert it to a date type. For example, you have a string type column,Date, that needs to be converted to a date type. The format of your values is MM-DD-YYYY. STR_TO_DATE(`Date`,'%m-%d-%Y') If your values are formatted as YY/DD/MM, then use STR_TO-DATE(`Date`,'%y/%d/%m') After you have a date type column, if you want to change the format, use the DATE_FORMAT() function. This function can accept and generate any date format needed. Your Date column is formatted as DD-MM-YY, but you would like to see it as MM-DD-YYYY DATE_FORMAT(`Date`,'%m-%d-%Y') To learn more about which date format specifiers to use, such as %m or %y, see Date Format Specifier Characters in Beast Mode.

​
Field name vs. String Value vs. Numeric Value.

In calculations, the actual field name is referenced using the backtick (` ). Inputting a hard string value requires single quotes (‘ ), while referencing a numeric value requires no quotes. 
CASE WHEN `Return Customer?` = 'Y' THEN 1
WHEN `Return Customer?` = 'N' THEN 0
END
Note: Putting single quotes (’) around numeric values will NOT result in an error but will output the 1s and 0s as a string-type column.

​
Strings Compared to Dates

When using the DATE_FORMAT() function, this converts the value to a varchar datatype, also known as a string. This is problematic because you can longer use the value as a date such as for comparisons. If your column should be a date format after using the DATE_FORMAT() function, then you need to use STR_TO_DATE() to convert it back to a date type. Using the following calculation, the output would be a string-type column. DATE_FORMAT(`Date`,'%m-%d-%Y') To convert the string to a date, we would want to wrap our entire calculation in the STR_TO_DATE() function. STR_TO_DATE(DATE_FORMAT(`Date`,'%m-%d-%Y'),'%m-%d-%Y') In the case that you have a string column that needs to be converted to a date column and you want it formatted differently, you must do the following: STR_TO_DATE(DATE_FORMAT(STR_TO_DATE(`Date`,'%y-%m-%d'),'%m-%d-%Y'),'%m-%d-%Y') Because it works from the inside out, the `Date` column, which is formatted as YY-MM-DD is first converted to a date. We would like to see the column as a MM-DD-YYYY format, so we then use DATE_FORMAT() to do so. However, now we are left with a string-type column. Lastly, we will use the STR _TO_DATE() function to return it to a date type.

​
Use of Trim Function

Many times, strings will leave unwanted spaces at the end or beginning of the value. This is problematic when using formulas that call out the exact value. The trim() function will trim off any unnecessary white space. For example, if the Zipcode column contains values that have white space, such as ‘12345 ’. Knowing white space exists in your values is extremely important if you are, for example, using the COUNT() function. COUNT() will count the number of characters in a string, including white space. By using the trim() function first, you ensure that you count only the number of valid characters in your Zipcode values. COUNT(TRIM(`Zipcode`))