This article lists the most common problems users have when adding Beast Mode calculations in Domo and provides suggestions for resolving these issues.
-
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 error out if it doesn’t include these elements or if it is not written correctly.
You can include as manyWHEN
conditions as you would like but must have aTHEN
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.
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')
Once you have a date type column, and you would like 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')
For more information on which date format specifiers to use, such as%m
or%y
, see Date Format Specifier Characters in Beast Mode. -
Use of Field name ( ` ) vs String Value ( ‘ ) vs Numeric Value.
In Beast Modes, the actual field name is referenced using the back tick ( ` ). Inputting a hard string value requires the single quotes ( ‘ ), whereas referencing a numeric value requires no quotes at all.
CASE WHEN `Return Customer?` = 'Y' THEN 1
WHEN `Return Customer?` = 'N' THEN 0
ENDNote: Putting single quotes (') around numeric values will not result in an error, rather it will just output the 1's and 0's as a string type column. -
Strings compared to Dates
When using the DATE_FORMAT() function, this will convert the value to avarchar
datatype, also known as a string. This is problematic because you are no longer able to use the value as a date for comparisons, etc. If your column should be adate
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 beastmode, 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 beastmode in the STR_TO_DATE()function.
STR_TO_DATE(DATE_FORMAT(`Date`,'%m-%d-%Y'),'%m-%d-%Y')
In the case that you will have a string column that needs to be converted to a date column and you want it formatted differently, you will need to 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 useDATE_FORMAT()
to do so. However, now we are left with a string type column. Lastly, we will use the STR_TO_DATE() function to bring it back 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. Thetrim()
function will trim off any unnecessary white space.
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 theCOUNT()
function.COUNT()
will count the number of characters in a string including white space. By using thetrim()
function first, you ensure that you count only the actual number of characters in your `Zipcode` values.
COUNT(TRIM(`Zipcode`))
Comments
0 comments
Please sign in to leave a comment.