Skip to main content

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

  

Hi,

Thanks for theses informations about the column specific data in import. It’s very reassuring to know that Pigment has this force.

However, I still can’t import my dates in the format you mentioned ( yyyymmdd ). Can you help me with this issue ? Is that a bug or something else ?

Thanks !

 


Hi Ali,

Thank you for bring this to my attention.  The Custom format is case sensitive for Months being capitalized and years and days are lowercase.  So your format should be yyyyMMdd, I am going to update the documentation now. 

Thank you! 

Chris   


Hi, 

Can we set multiple prefix or suffix format (with a separator) if we have in a same column different currency for example ($,€,£...)?

Otherwise I would be forced either to reformat the source file or to import the column in Text and then remove currency symbols in formula to have a number format.

Thanks! 🙂


Hello @Oliver
Today the import feature don’t support this usecase. You will have to use one of the two workaround you listed if you have a column with multiple currency. 
Please open an Idea to gather more vote from the community, so that we prioritize it accordingly 😀

Thanks!