The Convert Function of the Analytics Edge Core Add-in lets you convert text, numeric and date columns in a wide variety of ways. This article describes how it can be used in your Analytics Edge macros.
While building an Analytics Edge automation macro, if you find yourself with a column of text, numbers or dates, and the data is not in the format you want, the Convert Function may help.
When you open the wizard, it lists all of the columns available — select one and click the Next button. Depending on the type of data in the column (text, numeric or date), you will be presented with the options to convert to the same or a different type of data.
When converting within the same data type, you get a variety of common options:
Text to Text
Trim leading and trailing spaces; useful if you want to remove spaces or remove tabs from the beginning or end of your data.
Remove new line characters; useful if there are new line or carriage return characters in your data that can mess up data exports and report formatting.
Remove non-printable characters; if you get your data from sources that may contain odd, non-printable characters, then can be stripped out with this option.
Truncate to <n> characters; if your text may contain really long strings that can mess up report formatting, you can cut them off at a length of your choosing.
Change to upper case; for when you want to change all the text to uppercase letters.
Change to lower case: for when you want to change all the text to lowercase letters.
Number to Number
Round to number of decimals <n>; typically used to round a number to 0 (a whole number with no decimals) or for currency display purposes (e.g. dollars and cents).
Round to nearest ten / hundred / thousand / million; typically used to group metrics by stripping out small differences.
Date to Date
Change to first of the month; useful to group dates into monthly buckets.
Date only; strips off the time component of a date-time entry, which then makes it possible to compare entries on the same day.
Time only; Strips off the date component of a date-time entry (which makes the date seem like date ‘0’ in Excel, or 1900-01-00)
Convert GMT to local timezone; sometimes log files can contains dates and time in GMT (UTC). This option allows you to convert them to local time.
Convert local time to GMT; sometimes you need to convert from local time to GMT (UTC) for reporting or comparison purposes.
Sometimes you want to convert one type of data into a different type of data. These options are also possible with the Convert Function.
Text to Number
Convert text to number <Culture>; sometimes numbers are provided as text strings and you need them as numbers. This option allows you to convert them. Selecting a culture allows you to convert numbers that may use different thousands and decimal separators (e.g. 1,000.00 vs 1.000,00)
Extract numeric portion of text; sometimes the text column contains other text, like a currency ($23 USD). This option lets you extract just the numeric portion.
Text to Date
Source date format <string>; when dates are provided as strings with odd formats, this option lets you convert them to a date that Excel will recognize. Enter a series of code letters to mimic the format that your string uses. See Codes will open a help page with the code options available.
Convert GMT / local time to local / GMT time; these options can convert the date-time string to local or GMT (UTC) time zone.
Number to Text
Format desired <string>; this option allows you to convert a numeric column into a text column with a specific format. Maybe you want to display it with a currency symbol, leading zeroes, or a fixed number of decimal places.
Culture <Culture>; use this option to influence what characters are used for thousand and decimal separators (e.g. 1,000.00 or 1.000,00).
Number to Date
Treat number as Excel serial number; if the column contains some Excel date information, but the cells are not formatted as Date, they will appear as numbers. This options allows you to convert them back into Excel dates.
Treat number as days since <date>; this option allows you to offset a specific date by the number of days in the data.
Treat number as <option>; this option lets you get a date from a number that represents a day of the week, day of the month, month of the year, or even the year itself.
Date to Text
Desired data format <string>; enter a series of code letters to present the date in the specific format you want. Since this will be converted to a text string, it will not be affected by Excel date formatting or the regional settings of the computer.
Convert GMT / local time to local / GMT time; these options can convert the date-time string to local or GMT (UTC) time zone.
Date to Number
Convert to Excel serial number format; used to keep the Excel date but present it as a numeric value (possibly to preserve the date through an export/import cycle).
Convert to <option>; this option allows you to extract the hour, day of the week, day of the month, or day of the year of the date values, among other options.
Days since <date>; this allows you to calculate the number of days since a specific date. It can also be used to get the number of days until a specific date.
Combinations Also Possible
When working in an Analytics Edge macro, you can use multiple Convert functions, one-after-the-other, to get things you need. For example, you can convert a numeric column to the nearest thousand, then convert that to a text column for use as a grouping or category.
The Convert function wizard contains a wide range of very powerful conversion capabilities.