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.

Import_where_Months_are_lineItems_46243.xlsx

Accepted answers

All comments

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

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

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

source_months_as_columns_108981.PNG

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

source_months_as_columns_v2_109056.PNG

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

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…