topic Re: Joins in QlikView

Joins

Mon, 14 Jan 2013 12:50:47 GMT

Hi,

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.

Thanks!

Fredrik

Re: Joins

ToniKautto — Mon, 14 Jan 2013 13:27:39 GMT

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.

Re: Joins

Mon, 14 Jan 2013 13:43:50 GMT

Hi Toni and thanks,

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.

Fredrik