Mapping issues

neg177
Contributor

Mapping issues

Hi,

 

I'm having trouble mapping this excel sheet. Attached is a snap shot of what I'm trying to map and also the excel sheet I'm trying to map. Please advice. 

 

Thank you 

4 REPLIES 4
JaredDolich
Master Anaplanner/Community Boss

Re: Mapping issues

@neg177 

This is a tough one, so I'll give you what I think is your best strategy. 

  • First, the primary problem your facing is that you are using a Comma Delimited file and your text values have commas in them, not to mention you have a lot of special characters on the dollar figures, like "$". Commas are okay for numeric values.  Anaplan is parsing your file thinking that each column is separated by a column. So, you guessed it, you have to use a different delimiter, or you have to remove the commas in the text values and the special characters on your numeric values.
  • Second, I would encourage you to reformat the spreadsheet. Remove column 1, for example. And remove all the rows that are summary values or empty. Also remove any other columns you don't need. If this is a spreadsheet that is a template and you'll have to do this often, I would recommend creating a macro that can reformat all future spreadsheets. Just turn the macro recorder on before you format the sheet.
  • Lastly, Add a unique identify in the first column that identifies what makes that row unique. You don't need the time hierarchy in that since you can dimensionalize time. Create a list from that first column, and a system module that contains the properties.

Hope some of this helps. You can do this!

 


Jared Dolich
ChrisHeathcote
Community Boss

Re: Mapping issues

@neg177 

You are most likely have issues with mapping your time dimension. A few things to consider;

 

  • Use a custom time mapping where you specify which part of the time header is day (d), month (m) and year (y). At first glance attempt DD/MM/YY
  • Remove the first column from you data as it will cause your mapping to return an error as the import will try and match this to a time period.
  • If you can make your time reference another column and map this into time instead of using the column headers option.

Try a few interations of the time mapping 

neg177
Contributor

Re: Mapping issues

I believe I'm just going to copy and past it into another csv file that as the same exact layout I have in Anaplan, and use that as my flat file. 

ben_speight
Super Contributor

Re: Mapping issues

To start with you'll need to set the text delimiter option in the file import data source settings to double quote (")

Note the time column heading is inconsistent in the file with a two-digit year until 3/14/19 and four digits thereafter - change to 4 digits throughout and a M-D-Y date format should work.

Anaplan's importer should cope with dollar signs (ignore) and parentheses (negate) in numeric cells.

I can't help on the line item front, though