(a) There are two levels in the hierarchy, let's say "Level 2" and "Level 1", where Level 2 consolidates up into Level 1;
(b) Level 2 and Level 1 are both numbered lists with a display name property
(c) The members of the lists will be added to over time, and I want to update the members and their consolidations from a CSV that has two fields, representing Level 1 and Level 2.
(d) The fields on the CSV match up with what's in the Display Name property of the two levels.
(e) In a small number of cases the length of the fields is greater than 60 characters, which is why I'm trying to use numbered lists.
I'm trying to do build/update the lists by importing the CSV into the list that represents the lower level (Level 2), but how do I set up that import? I've tried it as follows in the Anaplan Import dialog box:
Nothing maps to LEVEL 2
The field for LEVEL 1 maps to Level 2's "Parent"
Nothing maps to Level 2's "Code"
The field for LEVEL 2 maps to Level 2's "Display Name"
Items uniquely identified by "Combination of Properties" (specifically, "Display Name")
This doesn't work; Level 2 items don't get associated with the right Level 2 parent, and indeed I get an "invalid parent" error. I can understand this, because I'm not putting the real Level 2 key into the parent field, I'm putting the Display Name in there, but it's the only identifier I've got.
First, please do not use a combination of properties as that means you have to have properties defined on the list which they shouldn't be and that you don't have a code for the member. All metadata should be in SYS Property modules. Second, you need to have a code for all lists, that is best practice. If you don't have a code, then it is very hard to get data out of numbered lists, no lookups. I would suggest the following:
Create a flat list for all members of L2, this can be numbered, but you must have a code from the source system.
Create a module using L2 in the Applies To, and if it is numbered, create a line item named Code with the formula code(item('L2 list name')). You will use this field to anchor the import of metadata, like the parent as well as any other data.
Create new line items, using the function ISFIRSTOCCURRENCE() to only get the first occurrence of the L1 member. Create a view of this and use this as the import into L1
Create a view, again off the same module, to build out the L2 members. In this, you will need code of the L2 member, the display name (if you want to do this correctly, make the Display Name list formatted back to the flat list you created in step 1 and match the code of the L2 member to the Display Name - it will automatically resolve), and supply the code of the parent for the parent.
I've tried your suggestions and didn't have any success, but I think my problem might be a more fundamental one in that I don't have a unique code for the higher levels. In the source system the higher level members are uniquely identified by a string, but in some cases that string is longer than 60 characters, and my understanding is that I therefore can't use it as either a name or a code. Is that correct?
Yes you are absolutely right!! Lists code limits to 60 characters. If you really don't have the unique identity within 60 characters, then it is hard to bring in the list items. But still either you have to ask the 3rd party system to provide the unique identity within 60 or you have to do some brainstorm on how to compress that more than 60 characters to less then 60 characters. As @rob_marshall mentioned, it is always best practice to have codes and don't go with combination of properties(Use only in case of flat file upload without unique identifier).
Tips to create codes:
1. You can take 1st 50 characters and length of the string (right(50)_Length),
2. Take 1st 25 letters and last 25 letters and then length of the string (left(25)_right(25)_Lenght)
3. Take last 50 characters and length (right(50)_length) etc...
Note: I am suggesting this approach cos you don't have unique identifier less than 60. Try with any approach and make sure the codes are unique before you load into the list so that you will get all the items.
After creating this list L1, if you try to do the upload into the L2, by mapping Disp Name to parent, it will not work. Hence get that file into the system as a flat file (Create list and module)and apply the same condition to get the code of L1 (In one of the line item) then do your import into L2 from this module's saved view.
I hope this helps you and in this way you can get rid of you concern.
I've run into similar issue to what you described, and depending on the situation different approaches will make sense.
While I won't advertise these as a flawless approaches, we've had success:
1. Identifying items based on a unique combination of properties, as well as 2. Having a module in Anaplan that takes long, text-based unique identifiers, and assigns a shorter numeric code (e.g. "000123"), and implementing logic across the model that enables us to work with those shorter codes as opposed to the longer text-based identifiers
Both required an understanding of the pros and cons, and a well-thought-out approach, and at the end of the day worked well for our purposes.
I just wanted to call out that these approaches can work, so long as the cons/down-sides are well-understood, sufficiently-mitigated, and don't pose risks to scalability of the model over time.
I was having a similar conversation with someone this morning
When you don't have a code or the code is greater than 60, you really are limited in the options and pretty much have to use the combination of properties route.
The best advice, aside from the above, is to keep the initial data in a separate model and try and generate your own code for use in the downstream models. You can have the large key as an attribute and use that as a display name if you need to
I've also used the numbered list ID as the code in the downstream model to ensure that it always matches (which is fine as long as you don't delete anything in the source). Even if you are creating a number list in the target, you can populate it with a code from the hub
So in you hub the first item may be #75, but when you create this for the first time in the spoke you can force the code to #75 instead of #1
@DavidSmith, I have quick question on your thoughts. If I use the system generated Id as a code from Hub to Spoke Model then how can I manage the future imports regarding that list. Ex. Say if I have created an item with the code as "#75" in spoke model. Now assume, I am getting another file to import other attributes of this list. In this case again the uniqueness is only my name which is more than 60 characters and if I go with the above approach how can I get the data respectively. Or yo can take this use case where we have to map the parent item.
Future imports (using the same combination of properties) should not generate a new item in the list as the combination of properties is the same as already exists, so the data against #75 should still be imported correctly (hence why I said don't delete this item)
Any new combination of properties will generate a new # number at the bottom of the list
All of this, as we have discussed, is a bit of a pain, hence, why it is so much better and cleaner to use a code in the import itself
Thanks for the response. But I can't use combination of properties when I go to next level of hierarchy. Consider I have created the List L1 with the code from Hub. Now I want to create L2 items under L1 using different file. How will I proceed in this scenario? I think the whole conversation is regarding this scenario.