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

JensitSebastian

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 Account	GL Account Name	Month	Cost Center	Product	Location	Amount (USD)	Tagged Project Code	Tagged Project
5000-01	ABC	18-Jan	XYZ1000	Product A	INDIA	10	Default	Default
5000-01	ABC	18-Jan	XYZ1000	Product B	INDIA	20	Default	Default
5000-01	ABC	18-Feb	XYZ1000	Product A	INDIA	160	Default	Default
5000-01	ABC	18-Feb	XYZ1300	Product A	INDIA	170	Default	Default
5000-01	ABC	18-Feb	ABC1000	Product B	INDIA	180	Default	Default

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

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