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.

Answers

  • 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

  • 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

  • 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

     

     

  • 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 ?

  • 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

  • @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

  • Alex,

     

    Thank you for the reply.  This is very helpful.

     

    Thanks!

    Mike