How to cross-join 2 lists

abinchen

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.

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

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.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.

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))

 

1. 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

 

 

 

 

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.

AnkitGupta

@SathyaM I tried this but am getting some error.

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!