fixing table with 2 different kinds of information

[Mikis]

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?

[BenMoss]

Can you confirm for company 2 the brand in your final file should be A2 and not A1? 

Ben

[BenMoss]

Why does A2 have a value against the D1 column that is A?

[Mikis]

Yes indeed that was a typo :)

company 1 should have brand a1 & a2

[Mikis]

Eh sorry, I mistyped quite a bit it seems

I'll correct it asap

[BenMoss]

I think this is what you are looking for, but take a look at let me know where it falls down if it's not right!

Ben

[Mikis]

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?

[BenMoss · Accepted solution]

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

[Mikis]

@BenMoss

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