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:
If anyone can give any solutions or workarounds that will be appreciated. Thanks a lot in advance.
@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
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.
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