topic Natural Join Problem - not joining rows in QlikView

Natural Join Problem - not joining rows

Anonymous — Wed, 20 Apr 2011 07:16:01 GMT

I am creating a Link Table from multiple fact tables. I am basically using JOIN to get the combined keys in the same rows with their common parts. But it seems that some of these parts are creating new rows even when the like columns have the same information.

So I am ending up with a link table like this:

KeyStoreDateProd KeyDateProduct Store Date Product

1 456 1/2/2000 123

1 1/2/2000 123

Instead of this:

KeyStoreDateProd KeyDateProduct Store Date Product

1 1 456 1/2/2000 123

The kicker is that the behavior is not consistent. Some rows are merging properly and others are being added. Is there any behavior or loading protocols I should be looking for? I am using: JOIN (LinkTable) LOAD

Natural Join Problem - not joining rows

Miguel_Angel_Baeyens — Wed, 20 Apr 2011 07:43:31 GMT

Hello Aaron,

In QlikView, a JOIN is very similar to a SQL OUTER JOIN, so in the case above, if you have two different lines with KeyStoreDateProd and KeyDateProduct with value 1, the JOIN LOAD will create all possible combinations of both.

If you have two keyfields, I'd create one composite keyfield, so the combination of all possible values is done on one field only, instead of two or more.

Hope that helps.

Natural Join Problem - not joining rows

Wed, 20 Apr 2011 09:10:43 GMT

I believe it would be done by Group by clause. Something like

join(link)

max(store), date, product

load from [ ]

group by date, product;