Join 2 data tables with no unique identifier, unequal number of rows, need each possible combination

Daniel_106904

I would like to join two data tables, each have 1 column with unique data. The tables have an unequal number of rows. And I would like to get each possible combination as output.
Say table 1 has values 1, 2, 3 and 4. And table has values A, B, C. The output would need to become 1A, 1B, 1C, 2A, 2B, 2C, 3A, 3B, 3C, 4A, 4B and finally 4C.
How would I do that?

Brandon_189578

Hi Daniel!

Just to clarify, say you have the following two tables:

Table 1
| A    | B    | C    |
|------|------|------|
| foo1 | foo2 | 1    |
| foo3 | foo4 | 2    |
| foo5 | foo6 | 3    |
| foo6 | foo7 | 4    |


Table 2
| C    | D    | E    |
|------|------|------|
| A    | bar1 | bar2 |
| B    | bar3 | bar4 |
| C    | bar5 | bar6 |

You want the output of this operation to be a new table:

Table Result
| C    |
|------|
| 1A   |
| 1B   |
| 1C   |
| 2A   |
| 2B   |
| 2C   |
| 3A   |
| 3B   |
| 3C   |
| 4A   |
| 4B   |
| 4C   |

If that is the case, the easiest way would probably be to first create an empty data table with the desired columns configured (1 text column named "C").

Next, use a table iterator batch action to run a sub-workflow for each row in the input table 1.

Then inside that sub-workflow, use table actions to do things like:
1. Apply a formula to each row of table 2, using the fields that came from table 1 (e.g. a field named "C") to prefix/suffix each row's value of the unique column. For example: columns['C'] = fields['C'] + columns['C'];. This would set the value of each row's C column to be the value of the field C (the value of the specific row from table 1 that we are currently batching over) with the current row of table 2's C value columns['C'] appended.
2. Remove other columns from the output of #1 so you have just the single column.
3. Add the rows from #2 to the empty table created from the beginning in the parent process.

Once the iterator finishes, the empty table should have all of the combinations of unique values.

If that is exactly what you are looking for, I can go into more detail of the above. I just wanted to make sure this is going in the right direction and you didn't need to keep all other row data or something similar.

Regards,
Brandon