topic Date join between tables in QlikView

Date join between tables

Tue, 15 Jan 2013 10:11:43 GMT

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

Re: Date join between tables

Gysbert_Wassenaar — Tue, 15 Jan 2013 13:41:39 GMT

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.

Re: Date join between tables

Wed, 16 Jan 2013 08:01:28 GMT

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.

Re: Date join between tables

Wed, 16 Jan 2013 08:17:28 GMT

Hi,

what result do you get instead?

Re: Date join between tables

sujeetsingh — Wed, 16 Jan 2013 08:44:45 GMT

paste a Sample

Re: Date join between tables

Wed, 16 Jan 2013 09:55:15 GMT

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.

Re: Date join between tables

Wed, 16 Jan 2013 10:15:54 GMT

Hi,

any set analysis in the chart's fields?

If not: Screenshot?

Ciao

Klaus

Re: Date join between tables

Wed, 16 Jan 2013 10:24:56 GMT

Hi,

No set analysis

Re: Date join between tables

er_mohit — Wed, 16 Jan 2013 10:30:37 GMT

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

Re: Date join between tables

Wed, 16 Jan 2013 10:49:46 GMT

Hi,

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

Re: Date join between tables

er_mohit — Wed, 16 Jan 2013 11:22:18 GMT

use after load table 2 in th end before semi colon write this where exit(SalesID);

Re: Date join between tables

Wed, 16 Jan 2013 11:36:54 GMT

Hi Frederik,

please get a listbox for the date in Table 1 - Dates and the Date in Table 2 - Sales

Would be interesting whether they are the same and what dates belong to 2012 Oct.

Ciao

Klaus