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