Join/Append 2 Lists
This is an usual question but I have 2 lists (1 customer & 1 future revenue to be derived off future sales) which I want to append into 1 Phantom/Clone list where I can then Lookup the values from 2 modules & review/analysis the data in 1 module
Is there any methodology to create such a list?
Do see if the following works:
1. Customer Sales is a module with 1 list, i.e. 'Customer'; Fake Time Sales is a module with 1 list, i.e. 'Fake Time'.
Select Customer/Fake Time is a module with 1 list, i.e. Customer/Fake Time. This is where you either select a 'Customer' or a 'Fake Time', the selection is shown in 'Final Selection', and Sales will grab from 'Customer Sales' or 'Fake Time Sales'.
2. Customer/Fake Time is a numbered list which grab 1 selection from 'Select Customer/Fake Time' as Display Name.
3. Blueprint of 'Select Customer/Fake Time'.
The modification i suggest below assumes you're inserting 'Customer' and 'Fake Time' manually in the model. If you're loading 'Customer' and 'Fake Time' from Data Hub or via Anaplan Connect, then you can just run a process (with 3 actions) that inserts 'Customer', 'Fake Time' and 'Customer/Fake Time'. Essentially you're adding new members in all 3 lists, so that the same 'Customer A' is created in both 'Customer' and 'Customer/Fake Time'.
Do see if the following work:
1. I've added 'Count' line item in all 3 modules.
2. A new module that compares the number of 'Customer/Fake Time' against 'Customer' + 'Fake Time' is created.
3. In the dashboard, you can check if 'Load Customer/Fake Time?' is true or not, if it is then you can click on 'Add Customer/Fake Time' process.
3. The process is made up of 2 actions, one of which is the creation of 'Customer/Fake Time' list from 'Customer' list. The 2nd action is the creation of 'Customer/Fake Time' list from 'Fake Time' list.
Can you give more context and illustration of the requirement. Is it just brining data from 2 lists into 1 combined list, also will the scenario include duplicates in the 2 source lists.
There a 2 different lists, 1 is numbered and 1 is standard. They are used in different modules to to calculate revenue from different sources
I need to combine them so i can bring data from both source modules & review them in 1 module
There are 2 duplicates
Let me know if this is not clear
Hi @MarkTurkenburg ,
Can you give some screenshots, the issue is still not clear.
Since you are already using both lists in the same modules, I would suggest to create a line item, text formatted (yes, I know what the plannual says about text) and concatenate either names or codes of the items from the lists you want to merge together:
Then display the view of the module so the 2 lists to be combined are nested in rows and only the leaf level items are shown. In columns you can display the Line Item with the concatenation of names or codes along with other Line Items that you would need as attributes for your new list (YEs, I know what the plannual says about properties in Lists 😛 ), like for instance the name of each of the individual items that compose the combined item. Something like this:
Then save the view. Now, Go to the settings tab of your module and to the new list you have created to host the combinations of List 1 and 2 and hit "import". Select the saved view from step above:
Is up to you if you want to identify your newly created items just by name, code or both in the mapping step:
Your final list will look like this. You can get the items of the original lists (to perform look-ups, sums and this sort of stuff across modules) either by loading them as properties from the source module (As per the example above) or using FINDITEM on the Concatenated Name/ Code.
Just be aware that for your numbered list, the NAME function retrieves the internal code that ANaplan assigns to items in numbered lists, so i would suggest you to use a combination of Codes.
I hope this helps to overcome you problem.
Thanks @AlejandroGomez for the detailed reply, it is a great example for that use case but unfortunately I was not clear enough in my original post & I'm not trying to combine to lists (e.g. List item 1 from List A & List item 1 from List B to become List item 1 & A in List 3) but to create a list which has each item from the 2 original lists into a new list. Let me explain further by example:
We have a #Customer Account list & an #Alternate Time Period list. Both lists are used in separate modules to forecast revenue for future periods.
#Customer Account list is used for current customer forecasts &
#Alternate Time Period is used to determine revenue from future sales that are not allocated to a specific customer account
I wish to create a module whereby I can view the revenue from all the list items in both lists in 1 module. My thought was I could create a #Customer Account & Alt Time Period list whereby each item from both lists is in the list with a property that links it to the original list, thereby allowing me to view revenue from both lists within 1 view
#Customer Account List
#Alternate Time Period list
Example of Joined list0
@LipChean_Soh , good explanation. Also will it help in the third list (composite list) to have all the customer as parent and adding the required fake time as child. In this way you could still get an aggregation of total revenue by customer. Moreover, if required we could still have more time range to a particular customer using the child list (numbered list).2
As you can see from the 'Example of Joined List' provided by @MarkTurkenburg , the member in the 3rd list is either from the 1st list OR from the 2nd list.
Unless i'm mistaken, there's no parent child relationship between Customer and Fake Time.
Thanks @LipChean_Soh for the detailed response, it works quite well
The main issue I was having trouble with was creating a method whereby the Customer/Fake Time list could be updated automatically by an Action. In your example I believe I would do the following to create the Customer? & Fake Time? line item values
1) Create a module with the #Customer Account as the dimension and return the List value as the line item. Then copy all the values & paste into the Customer? line item
2) Repeat the same for the #alternate Time Period into the Fake Time? line item
This works fine but is not dynamic & requires the manual work around of copy/paste. Are you aware of any method whereby I could return values to the Customer? & Fake Time? line items via an Action?
Thanks @LipChean_Soh - fantastic solutions!