Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be
Sum(If(product1 = product and Location = Location1, 1, 0)
Or
Count(DISTINCT If(product1 = product and Location = Location1, 1, product)
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!
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
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.