Hello,
I have a data table that contains 2 kinds of information: information on companies and information on brands (example attached).
If the row is a company row, then the brand facts are blank.
if the row is a brand row, then the company facts are blank.
I also have a second table that tells me how companies & brands are related.
A company can have several brands and a brand can have several companies.
What I would like is to have a table with all combinations of companies & brands.
Perhaps an example works best (example data attached)
sourceData:
| dim1 | dim2 | dim3 | company | brand | companyfact1 | companyfact2 | brandfact1 | brandfact2 |
| A | C | X | 1 | | 7% | 81% | | |
| A | D | Y | 1 | | 44 | 82% | | |
| A | C | X | | a1 | | | 33% | 92% |
| A | D | Y | | a1 | | | 53% | 94% |
| A | C | X | 2 | | 41% | 53% | | |
| A | D | Y | 2 | | 18% | 30% | | |
| A | C | X | | a2 | | | 91% | 17% |
| A | D | Y | | a2 | | | 72% | 5% |
companyBrand
I want to build the following table:
| dim1 | dim2 | dim3 | company | brand | companyfact1 | companyfact2 | brandfact1 | brandfact2 |
| A | C | X | 1 | a1 | 7% | 81% | 33% | 92% |
| A | D | Y | 1 | a1 | 44% | 82% | 53% | 94% |
| A | C | X | 1 | a2 | 7% | 81% | 91% | 17% |
| A | D | Y | 1 | a2 | 44% | 82% | 72% | 5% |
| A | C | X | 2 | a1 | 41% | 53% | 91% | 17% |
| A | D | Y | 2 | a1 | 18% | 30% | 72% | 5% |
Does anyone know how to proceed?
