Natural Join Problem - not joining rows

[Anonymous]

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

[Miguel_Angel_Baeyens]

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.

[#3]

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

join(link)

max(store), date, product

load from [ ]

group by date, product;