I need help with a join between two tables. Below is what I have.
Table 1:
Date
Table 2:
SalesDate,
SalesID
ProductID,
The Table 1 contains all dates from 20110101 - today. Table 2 contains sales information with the date of the sale and the product and customer ID - each row is unique. There will be some days without any sales and the table only contains dates with sales.
What I want to do is join these table together in a way where I later can create a table showing all Dates and the number of sold items per ProductID. The requriments is to show 0 sales for a Date and ProductID without any sales.
I have tried with a left join but without any luck. Hope someone can help me out.
I would generally not join in order to create empty or zero values in a table. Considering only these two tables it makes more sense to me to keep them separate.Instead introduce a common key field with the date values, that way you can present the zero result based om your chart expressions instead.
Thrust me I have tried that. I actually have a third table - Product.
Table 3 - Product
ProductID,
ProductName
The user should be able to chose a ProductName and see the sales result for a certain period. And for whose dates without any sales the result should be 0.
In my chart the dimension is Date and the expression i count(SalesID). I have choosen to show zero on Bars and unchecked the suppress zero-values. But it doesn't work.
