Sum of undependent Dimension

[r_pinzke]

Hello,

I have a question: I want to output in a table the data of Product1. Where Location1 = Location. And Product1 = product. I can not name Location1 and Product1, because there are Syn links. The key must be the year. How do I get the right amount? Can someone help me please?

DepA:
Load * Inline [

Year, Location, Product

2000, A, P1
2000, B, P2
2010, A, P1
2010, B, P2
];

DepB:
Load * Inline [

Year, Location1, Product1

2000, A, P1
2000, B, P2
2010, A, P1
2010, A, P1
2010, B, P2
2010, B, P2
2010, B, P3
2010, B, P3
2010, B, P3
2010, B, P3
];

Thank you.

Rico

[sunny_talwar]

May be

Sum(If(product1 = product and Location = Location1, 1, 0)

[sunny_talwar]

Or

Count(DISTINCT If(product1 = product and Location = Location1, 1, product)

[john_obrien]

If the issue is that the two tables should associate on 3 fields (year, location & product), then you can avoid a synthetic key by making a compound key on both tables like this:

DepA:

Load

     *,

     Year &'_'& Location &'_'& Product as _LinkKey

;
Load * Inline [
Year, Location, Product
2000, A, P1
2000, B, P2
2010, A, P1
2010, B, P2
];

Then the  two tables will link on only 1 field.

I hope that helps!

[r_pinzke]

Thank you so much.

The If query works great. Unfortunately, I can not use the compound key - thanks anyway.
One question: Can you do that via the so-called quantifiers? So for example: sum ($ {<Product1>} Product)?

Thank you. Rico

[sunny_talwar]

I think you mean set analysis, I don't think set analysis will work in your case because of the way your data is structured.