Hi there,
I have a table of data where we know which products a customer has purchased.
The transactions table has fields like this:
OrderID
ItemID
CustomerID
ProductID
ProductName
Value
We then have another table where we know the other products a customer is interested in. This table has no "measure" fields, so you can only count the CustomerID.
This interest table has fields like this:
CustomerID
ProductName
<More Other Dimensional fields>
One customer can have an interest in multiple products. The products in the interests table can be named the same as in the purchased table, or may be different.
How can I combine both of this pieces of information in one chart where we can show the products they have bought and the products they are interested in effectively?
I cannot create a composite key made of CustomerID & ProductName because the real data model is not as simple as that.
