How to cross-join 2 lists

Hi Anaplanners,

I am Simon from DXC. I am working for a requirement which needs to generate a cross-join list based on 2 lists. But I doubt if Anaplan can do this like SQL queries. I would like to take any workaround as well.

We have 2 lists: one is for Managment Nodes and another for Industry.

In Management Nodes there are 3 records: A, B and C

In Industry 4 records: 1, 2, 3 and 4

We need to cross-join these records and save them to another numbered list, this list should have 3*4=12 records like below:

A 1

A 2

A 3

A 4

B 1

B 2

B 3

B 4

C 1

C 2

C 3

C 4

If anyone can give any solutions or workarounds that will be appreciated. Thanks a lot in advance.

Accepted answers

DavidSmith

@abinchen Can I ask why you need to join them to create a numbered list? Often it is more efficient to model with the two dimensions separately, especially if the every Industry belongs to every Management Node

However, if you do need to "join" the lists, please don't use the solution above. This is an inefficient construct.

Please read the following article which explains the most efficient way to create the text concatenation that you need

https://community.anaplan.com/t5/Knowledge/Formula-Optimization-in-Anaplan/ta-p/41663

And, as an aside, it is always best to use codes if you can, especially for Numbered lists

I hope that helps

David

DavidSmith

@abinchen

To get the data into the new module using an import, you will need to have the join code (from above) as a line item within the source data module and use that field as the source mapping to the numbered list (hence why you need to create a code with a numbered list)

An alternative way is the model the data in the target.

When you create the numbered list, create a module by that list and create two line items holding the Management node and Industry, formatted to the respective lists

You can then use a SUM[] formula or a LOOKUP[] function to map to the source data

David

All comments

kavinkumar

HI @abinchen ,

Try it in this way!!

Step 1: Create a module with 2 list that you mentioned above as dimension. Then add a line item and make it as text.

Step 2: Apply the below formula. How to cross-join 2 lists.PNG Then Upload the Code line item directly into your numbered list.

Hope this sovles your problem, let me know if you have any concerns.

Regards,

Kavin.

DavidSmith

However, if you do need to "join" the lists, please don't use the solution above. This is an inefficient construct.

Please read the following article which explains the most efficient way to create the text concatenation that you need

https://community.anaplan.com/t5/Knowledge/Formula-Optimization-in-Anaplan/ta-p/41663

And, as an aside, it is always best to use codes if you can, especially for Numbered lists

I hope that helps

David

SathyaM

Hi Abichen,

Please refer the attached document with screenshots.

1. Create Management List

2. Create Industry List

3. Create a Module – “Cross- Join Module” with Management and Industry as Lists

-- Create 1 Line Item called Cross-Join – Format as Text

Formula is NAME(ITEM(Management)) & "" & NAME(ITEM(Industry))

Create a Numbered List called “Cross-Join”

Create a Property called “Display Name” – format as Text
Click on Import and select the Source as “Cross-Join Module” and map as per screenshot
Once you run this action, you will get 12 new items created in the “Cross-Join” List.

Kindly let me know if you have any questions.

Thanks

Sathya

Cross-Join_List_example_43257.doc

DavidSmith

@SathyaM @kavinkumar

Please see my previous post for the most efficient way of joining text.

David

abinchen

I need to cross-join a few nodes (0.01%) and industries as a temporary list, and then add the new mappings to existing mapping list, which is a small small portion of the whole. Your solution is also inspiring and thank you for the performance suggestion as well.

The last problem I am facing now is to import the newly created module data to the numbered list, since the new module does not have unique key which is needed when importing to a numbered list.

DavidSmith

@abinchen

An alternative way is the model the data in the target.

When you create the numbered list, create a module by that list and create two line items holding the Management node and Industry, formatted to the respective lists

You can then use a SUM[] formula or a LOOKUP[] function to map to the source data

David

AnkitGupta

@SathyaM I tried this but am getting some error. WhatsApp Image 2020-05-21 at 9.28.29 PM.jpeg

AnkitGupta

@SathyaM Figured it... Display Name should be in column (in the saved view of the joined module) for it to work.

Thank you for the detailed steps in the doc!

Quick Links

Can you help?

Unanswered questions

Has anyone taken a stab at Master Anaplanner Recertification exam yet?
Just curious :)…
Solution for indent diabled for hierarchical list when the page has more than two axis
Hello fellow Anaplaners, I have a question about the UX for Anaplan for hierarchical list. If I have a list with multiple layers, say P1, P2, P3, and P4, and the workpage had 2 axis, list and line items, in its vertical axis and time in its horizontal axis (see figure below) The list is shown without indentations, and may…
Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…