Date join between tables

[#1]

Hi,

Hope someone can help me with this issue. I have three tables that I wanna join together; the first one is the date table with all dates from 2011-01-01 to today, the second table is the sales table with all the sales information and the third table i the product table with information about the products.

Table 1 - Dates (contains all days from 20110101)

- DateID

- Year

- Month

- Day

- ....

Table 2 - Sales (contains a unique row for each sales)

- SalesID

- SalesDateID

- ProductID

- ....

Table 3 - Products (contains a unique row for each product)

- ProductID

- ProductName

- ....

One ProductID can have many SalesID and a ProductID may be sold one day and the next day there is no sales for that ProductID which means the ProductID will have no connection to a SalesDateID if there is no sales that day.

What I wanna do is to join SalesDateID with DateID so that I can create a chart showing the sales result for a choosen ProductID. The chart should show ALL days during a period and the number of sales - 0 if there were no sales that day.

It should look like this for at choosen ProductID:

Date	ProductID	Number of sales
2011-01-01	1234	456
2011-01-02	1234	73
2011-01-03	1234	12
2011-01-04	1234	0
2011-01-05	1234	908
2011-01-06	1234	0
2011-01-07	1234	454
2011-01-08	1234	17
2011-01-09	1234	0
.... and so on....

Hope someone can help me with the join or expression to solve this.

Thanks!

Fredrik

[Gysbert_Wassenaar]

Rename SalesDateID to DateID and simply load all your tables.

Dates:

load * from ...datestable...;

Sales:

load SalesID, SalesDateID as DateID, etc..

from ...salestable...;

Products:

load * from ...productstable...;

You can then use DateID as dimension in your chart and check the Show All Values option. You can add Product as the second dimension and count(SalesID) as expression.

[#3]

Hi,

Yes I could do that, but then the Date filter won't work. The user want's to filter on date period and the chart should then only show all the dates in that period.

[#4]

Hi,

what result do you get instead?

[sujeetsingh]

paste a Sample

[#6]

Hi,

For example when I chose the period oct-2012 the chart will show all the dates that exists - even those outside of the period - and give me null as ProductIID and 0 in sales where there is no sales. But the result in the dates inside the period shows me the correct numbers.

[#7]

Hi,

any set analysis in the chart's fields?

If not: Screenshot?

Ciao

Klaus

[#8]

Hi,

No set analysis

[er_mohit]

try this:

Table 1 - Dates (contains all days from 20110101)

- DateID

- Year

- Month

- Day

- ....

join(Table1)

Table 2 - Sales (contains a unique row for each sales)

- SalesID

- SalesDateID as DateID

- ProductID

- ....

where exit(SalesID);

Table 3 - Products (contains a unique row for each product)

- ProductID

- ProductName

.hope it helps.

see the attached file

[#10]

Hi,

That didn't work. I got the same result as my picture above