When working with data such as Salesforce data where data validation for US state input fields doesn’t always exist, you may need to ensure the data you’re presenting all rolls up correctly without seeing CA and California represented as distinct rows. Omni makes it easy to do these data transforms directly in the UI which just a few clicks!
To do this in Omni, you can use the below code to either:
- Create a new field
- Transform the field itself
Code we will be Utilizing
You will add your own Table and Field details below for each “TABLE.FIELD” from where you are pulling the state data.
CASE
WHEN ${TABLE.FIELD} = 'Alabama' THEN 'AL'
WHEN ${TABLE.FIELD} = 'Alaska' THEN 'AK'
WHEN ${TABLE.FIELD} = 'Arizona' THEN 'AZ'
WHEN ${TABLE.FIELD} = 'Arkansas' THEN 'AR'
WHEN ${TABLE.FIELD} = 'American Samoa' THEN 'AS'
WHEN ${TABLE.FIELD} = 'California' THEN 'CA'
WHEN ${TABLE.FIELD} = 'Colorado' THEN 'CO'
WHEN ${TABLE.FIELD} = 'Connecticut' THEN 'CT'
WHEN ${TABLE.FIELD} = 'Delaware' THEN 'DE'
WHEN ${TABLE.FIELD} = 'District of Columbia' THEN 'DC'
WHEN ${TABLE.FIELD} = 'Florida' THEN 'FL'
WHEN ${TABLE.FIELD} = 'Georgia' THEN 'GA'
WHEN ${TABLE.FIELD} = 'Guam' THEN 'GU'
WHEN ${TABLE.FIELD} = 'Hawaii' THEN 'HI'
WHEN ${TABLE.FIELD} = 'Idaho' THEN 'ID'
WHEN ${TABLE.FIELD} = 'Illinois' THEN 'IL'
WHEN ${TABLE.FIELD} = 'Indiana' THEN 'IN'
WHEN ${TABLE.FIELD} = 'Iowa' THEN 'IA'
WHEN ${TABLE.FIELD} = 'Kansas' THEN 'KS'
WHEN ${TABLE.FIELD} = 'Kentucky' THEN 'KY'
WHEN ${TABLE.FIELD} = 'Louisiana' THEN 'LA'
WHEN ${TABLE.FIELD} = 'Maine' THEN 'ME'
WHEN ${TABLE.FIELD} = 'Maryland' THEN 'MD'
WHEN ${TABLE.FIELD} = 'Massachusetts' THEN 'MA'
WHEN ${TABLE.FIELD} = 'Michigan' THEN 'MI'
WHEN ${TABLE.FIELD} = 'Minnesota' THEN 'MN'
WHEN ${TABLE.FIELD} = 'Mississippi' THEN 'MS'
WHEN ${TABLE.FIELD} = 'Missouri' THEN 'MO'
WHEN ${TABLE.FIELD} = 'Montana' THEN 'MT'
WHEN ${TABLE.FIELD} = 'Nebraska' THEN 'NE'
WHEN ${TABLE.FIELD} = 'Nevada' THEN 'NV'
WHEN${TABLE.FIELD} = 'New Hampshire' THEN 'NH'
WHEN ${TABLE.FIELD} = 'New Jersey' THEN 'NJ'
WHEN ${TABLE.FIELD} = 'New Mexico' THEN 'NM'
WHEN ${TABLE.FIELD} = 'New York' THEN 'NY'
WHEN ${TABLE.FIELD} = 'North Carolina' THEN 'NC'
WHEN ${TABLE.FIELD} = 'North Dakota' THEN 'ND'
WHEN ${TABLE.FIELD} = 'Northern Mariana Islands' THEN 'MP'
WHEN ${TABLE.FIELD} = 'Ohio' THEN 'OH'
WHEN ${TABLE.FIELD} = 'Oklahoma' THEN 'OK'
WHEN ${TABLE.FIELD} = 'Oregon' THEN 'OR'
WHEN ${TABLE.FIELD} = 'Pennsylvania' THEN 'PA'
WHEN ${TABLE.FIELD} = 'Puerto Rico' THEN 'PR'
WHEN ${TABLE.FIELD} = 'Rhode Island' THEN 'RI'
WHEN ${TABLE.FIELD} = 'South Carolina' THEN 'SC'
WHEN ${TABLE.FIELD} = 'South Dakota' THEN 'SD'
WHEN ${TABLE.FIELD} = 'Tennessee' THEN 'TN'
WHEN ${TABLE.FIELD} = 'Texas' THEN 'TX'
WHEN ${TABLE.FIELD} = 'Trust Territories' THEN 'TT'
WHEN ${TABLE.FIELD} = 'Utah' THEN 'UT'
WHEN ${TABLE.FIELD} = 'Vermont' THEN 'VT'
WHEN ${TABLE.FIELD} = 'Virginia' THEN 'VA'
WHEN ${TABLE.FIELD} = 'Virgin Islands' THEN 'VI'
WHEN ${TABLE.FIELD} = 'West Virginia' THEN 'WV'
WHEN ${TABLE.FIELD} = 'Wisconsin' THEN 'WI'
WHEN ${TABLE.FIELD} = 'Wyoming' THEN 'WY'
ELSE ${TABLE.FIELD}
END
Creating a New Field
When you’re in your workbook, on the left hand pane of all views and fields there is an option at the bottom called “+Add Field”. If you click this button, another menu will open to the left and you can add the above SQL in the input field where the arrow is pointing. From there you can give it a name and add to your workbook by clicking “Add”.
Updating a Current Field
When you’re in your workbook, on the left hand pane of all views and fields you can click on the triple dots next to the field you want to modify and then navigate to Modeling > Edit. From there, you’ll see the “Edit Field” pane come in on the left and you can update the SQL referencing the SQL field noted for the as is field. ie. in the below example, you’d want to reference “Table_name.BILLING_STATE”
Happy Querying!