Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to format numbers a number when concatenating with a string.
Problem:
When concatenating a string with a number the resulting value is a string and unable to utilize Domo's built in formatting.
Solution:
Check the length of the number using the LENGTH function and then get the appropriate segments of the number using SUBSTRING and then combining them all together using CONCAT
-- Author:
-- Created:
-- Last Modified:
-- Description:
-- Checks the length of a number to determine how to insert thousands separators (,) using SUBSTRING to get 3 digit pieces of the number.
CONCAT('Total Amount: ',
CASE WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 12 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 10, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 11 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 9, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 10 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 9 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 8 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 7 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 6 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 5 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 4 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) <= 3 THEN
ROUND(SUM(`random_number`), 0)
WHEN SUM(`random_number`) IS NULL THEN 0
ELSE 'ERROR'
END
)
Note:
These beast modes only go to 100 Billion (12 digits). Any number higher would need to expand using the same logic for higher lengths.
