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
company | brand |
1 | a1 |
1 | a2 |
2 | a2 |
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?
Solved! Go to Solution.
Can you confirm for company 2 the brand in your final file should be A2 and not A1?
Ben
Why does A2 have a value against the D1 column that is A?
Yes indeed that was a typo :)
company 1 should have brand a1 & a2
Eh sorry, I mistyped quite a bit it seems
I'll correct it asap
Hello @BenMoss
Thanks for your suggestion, but I need it to be a little more flexible.
I added 'companyfact' in the title, but I don't know in advance that it's a company fact.
Basically, if the value is blank then it should be a company fact
I managed to solve the puzzle using some of my colleagues, but there's one big performance issue.
I attached a workflow where I first generate ALL possible combinations of companies and brands, and then filter on only the relevant ones.
This sounds like a minor issue, but the dataset we're using is huge and this takes really long to calculate.
Do you perhaps have a quicker way to do the step below?
The append and join is doing nothing.
By creating all combinations, the join then becomes irrelevant, I don't understand why you are doing an append, that means you end up with detial seen as 'brand A' when in fact it's related to brand b.
Ben
eh...you're right
What I was trying to do was create ALL options using that append fields and then filter only the relevant combinations using the join
but it's clearly a lack of knowledge on how that append fields work.
For some reason I get all the relevant options straigth after the append. I would expect the combination of company 2 and brand a2 would also be included, but it's not (weird, but I wanted to exclude it anyway so all is well that ends well)
anyways, it's working now :)
Thanks for your time