Import a csv file to a module where "Months" are line items

gregoryTan
Contributor

Import a csv file to a module where "Months" are line items

Hi, Anaplaners

 

I am struggling with import data where one of its columns represents time "YYYYMM" to  a module where Time dimension is "Year" and "Month"s are a line items. Would you take a look at the attached to see what I would like to achieve ? Such an import ever possible ?

Thanks in advance.

7 REPLIES 7
LipChean_Soh
Certified Master Anaplanner

Re: Import a csv file to a module where "Months" are line items

Hi,

 

Perhaps you have a good reason why you're loading into Standard Year + Line Item months, but if you must, do see if the following works:

1. Load your csv file into 'Data Load' with Products, Standard Months as list.

2. In Month Properties, create multiple Line Item months with formula FINDITEM(Time, "Jan " & RIGHT(NAME(ITEM(Time)), 2)). Replace Jan with different months in the different line items.

3. In the Report 01 where you have Standard Year + Line Item months, do a LOOKUP of 'Data Load' by referring to the relevant Line Item Months in 'Month Properties'.

Screen Shot 2019-05-27 at 9.43.40 PM.png

 

Thanks,

LipChean

alexpavel
Certified Master Anaplanner

Re: Import a csv file to a module where "Months" are line items

Hi Gregory, 

 

Is always tricky when you need to pivot the months..Usually to me happened the other way around:

I sometimes receive source files with the columns as Months.. 🙂

However, you can achieve what you need, but in more steps:

 

1. Import the source file in a temporary module using a numbered list where all your source column will be line items

2. In the new module create a line item "Year" with the formula

     Year Sales = Left(Sales Month, 4)

3. Create another 12 new line items formatted as number for the months with formulas like this:

   Jan = if right(Sales Month, "01") then Sales Quantity else 0

   Feb = if right(Sales Month, "02") then Sales Quantity else 0

   Mar = if right(Sales Month, "03") then Sales Quantity else 0

.....

   Dec = if right(Sales Month, "12") then Sales Quantity else 0

4. Now you are able to import the data in your target layout

Create a saved view within this module and create an action to import your data into your target using the new line-items

  Your target module need to have lists: Product, Year and 12 line items as months

 

Hope this help!

ciao

Alex

mshaw
New Contributor

Re: Import a csv file to a module where "Months" are line items

Alex,

I know this is an old string, but I am hoping you can share how you have accomplished the opposite of the original question:  transform a source .csv with months as columns to a target module with month as a field?

 

Any help is appreciated.

Thanks!

Mike

 

 

nathan_rudman
Master Anaplanner/Community Boss

Re: Import a csv file to a module where "Months" are line items

do you have the real time dimension in your target module ?

 

If yes, Anaplan might recognize the months in your file, worse case you can do so manually in the loading screen.

 

If no, what is this time you then ?


Nathan Rudman, Anaplan Model Builder
mshaw
New Contributor

Re: Import a csv file to a module where "Months" are line items

Hi Nathan,

 

Thank you for the response.  Great question.  Current proposed structure of the target module only includes a record "ID" and does not include the real time dimension.  Time will be created as a property via formula.  Attached screen capture provides more detail.

 

Please note that the structure of the target module can be changed if there is a better way.  But the end goal is to transform the "crosstab" source into a more "database" style module.

 

Does that make sense?

 

Thanks again!

Mike

alexpavel
Certified Master Anaplanner

Re: Import a csv file to a module where "Months" are line items

@mshaw I this case you will need 3 modules:

 

1. D01: to store data from Source file and the months will need to be defined as numeric line-items.

2. D02: Creating a line-item subset, COLLECT() data from Source module. This way you will pivot data in rows for the months. (maybe you will need a mapping module for the line-item subset, if you need a different way to store data in final module like using the Anaplan Time list). 

3. D03: Final module to import data from the D02 module.  Here you can choose for the Time: property or use the Anaplan Time list.  Usually, I would choose for separate dimension for Time. 

 

Hope it helps

Alex

mshaw
New Contributor

Re: Import a csv file to a module where "Months" are line items

Alex,

 

Thank you for the reply.  This is very helpful.

 

Thanks!

Mike