Adjusting source files can be very time and labor intensive, this article explains how to import your special formatted data into formats Pigment can understand. These customizations are available in both list and metric imports.
Table of Content
What are column-specific data definitions?
When mapping data in an import, you can define the Number and Date formats for the entire import. However, sometimes you need more customization, you might need to have different date formats in different columns, or maybe you need a more specific definition of a number.
In step 2 of an Import, Map Data, you will notice an Options heading and an icon next to any number or date formatted mappings. In list imports, you’ll see it in properties and metrics will have it for Header mappings. You can change as many different formats as you need to.
| Importing into a List | Importing into a Metric |
|---|---|
| 
|
When data from a source file is not properly defined, the items in the column will appear as red and you will see a warning message at the top of the Mapping screen. This is a good indicator that you need to apply custom data definitions.
Number importing options
When customizing the fields for numbers, you will see the following options, below is a description of each option.
Reset - Located in the top right corner, this will revert any custom definition to the import defaults in the Number and date formatting at the top of the import.
Separator - Differentiates how numbers perceive commas and periods in the formatting of numbers.
Prefix - This is any symbol or letter that is used in front of the number. Example- $
Suffix - Any symbol or letter after the number, Examples - K or £.
% - Used on numbers that represent percentages. This will effectively move the decimal over two places. For example, 5 would be converted to .05
Multiplier Dropdown (Thousand) - The multiplier dropdown is used to multiply numbers that might have been shorted for appearance. For example, 5 might have been used to represent 5,000,000, in this case you would want to select the multiplier dropdown and change it to millions.
Multiplier Dropdown (Basis Point) - Choosing this option will divide your values by 0.0001 as one basis point= 0.01%. For example, if your value is 5, selecting Basis Point will give the following result: 50000 (5/0.0001).
After adjusting your columns, click Save. Once you hit Save, you will see the column adjust. If the column turns blue and is the outcome you expect, you are good to go! You can always use Reset to go back to the defaults and try again if necessary.
Note: There may be times in which you need to change multiple options. For example, 5% would need to be defined as a Percentage, and also would need to have the % symbol listed as a Suffix.
Date formatted Options
You can now have multiple different date formats within the same import, as well as custom-defined date formats. Similar to the number format options, once a property or metric is defined as date formatted, you will see the option to customize. With one click, you can choose FR, US, or GB formats. The real power comes in with the custom format, allowing pigment to recognize many more date formats.
Pigment can also recognize dates with the following new formats:
| Value | Patern |
|---|---|
| 2012 | yyyy |
| 2012-12-31 | yyyy-MM-dd |
| 2012/12/31 | yyyy/MM/dd |
| 31-12-2012 | dd-MM-yyyy |
| 20121231 | yyyyMMdd |
| 20031209T123000.000 | automatic |
| 2002-02-02 03:30:45 | automatic |
Note: Custom format is case sensitive. For years use lowercase y, months a uppercase M, and days are a lowercase d.
Example: to parse 20121231 , use the format yyyyMMdd
Data imports are now easier than ever!
Tip: After saving a data definition, you can see how Pigment will bring in the data within the column. Not looking how you would expect, simply hit Reset and try again. With numbers, it's important to remember that multiple settings might need to be applied. For example, 5K would need to have the suffix of K applied, as well as the multiplier selected and set to Thousand.
