Numbered List import issue while using 'Combination of Properties' with "Month" as unique identifier

JensitSebastian
JensitSebastian
in Integrations and extensibility

Use Case and Workaround: 
I am trying to import data into numbered list using "Combination of Properties" option. The catch is, my only unique identifier is "Month". I have records for a GL Account by Cost Center by Product and by Location. And will have same combination coming in as Trial Balance records but for different months.
If you see below the highlighted rows 1 and 3 the only way they are uniquely identified is by the month in which the entry is passed ( Jan 18 and Feb 18).

GL AccountGL Account NameMonthCost CenterProductLocationAmount (USD)Tagged Project CodeTagged Project
5000-01ABC18-JanXYZ1000Product AINDIA10DefaultDefault
5000-01ABC18-JanXYZ1000Product BINDIA20DefaultDefault
5000-01ABC18-FebXYZ1000Product AINDIA160DefaultDefault
5000-01ABC18-FebXYZ1300Product AINDIA170DefaultDefault
5000-01ABC18-FebABC1000Product BINDIA180DefaultDefault

While importing I was getting error "Another row has already been processed with this key".

Anaplan fails to evaluate the Month value while importing.

 

Workaround: Before importing I added a column CODE which was concatenation of GL Account , Cost Center , Product and Location and Month. Then while importing I used this CODE as numbered list's code. Please note the month was converted to numeric value by excel and hence I was able to identify them uniquely. 

 

Hope this helps and saves time of rebuild. 

 

Thanks,

Jensit

Tagged:

Answers

  • DavidSmith
    DavidSmith

    @JensitSebastian 

    Definitely use a code rather than combination of properties if you can; it is much faster and more efficient.

    However, I would advise against using the month as part of the key.  If you have read my blog about sparsity:

    (The Truth about Sparsity (part 1), The Truth About Sparsity (part 2))

    we have proved that in almost all cases, it is faster, and smaller to have a module dimensioned by time for the unique key (without month) and load the data values into that module

    Finally, we advocate not using list properties for the attributes, and calculating the attributes from the key in a module.   If you know the GL account is always 7 digits you can use the following to map directly again the GL account list

    GL account text = LEFT(NAME(Transactions),7)

    GL Account list = FINDITEM(GL Account, GL account text)

    This is more efficient for model opening as there are no text attributes stored

    If the GL account (or other attributes), vary, if you have a delimiter in the key, you can calculate the length, by using something like

    FIND("_",NAME(Transactions))

    I hope this helps you and other readers stage data in Data hubs more efficiently

    David

     

Categories