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

gregoryTan

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.

LipChean_Soh

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'.

 

Thanks,

LipChean

alexpavel

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

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

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 ?

mshaw

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

@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

Alex,

 

Thank you for the reply.  This is very helpful.

 

Thanks!

Mike