Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aaroncouran
Contributor III

Natural Join Problem - not joining rows

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

Tags (1)
2 Replies

Natural Join Problem - not joining rows

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.

Not applicable

Natural Join Problem - not joining rows

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

join(link)

max(store), date, product

load from [ ]

group by date, product;

Community Browser