topic Link Table issue in QlikView

Link Table issue

sifatnabil — Mon, 24 Aug 2015 07:11:41 GMT

Hi all,

I have 2 fact tables (Trades and Orders) that I'm trying to combine via a link table. There are a few common fields which I used to build a key that will link them, but one common field, the Order ID, has more values in the Trades table than the Orders table. As a result, when linking them, only the Order IDs that are common to both are showing up. How do I ensure that the other Order IDs are included?

This is what I used:

trades:

load

autonumberhash256(OrderID,Date) as Key,

...

from ...

orders:

load

autonumberhash256(OrderID, Date) as Key,

...

from ...

LinkTable:

load distinct

Key,

...

resident tradestable;

LinkTable:

load distinct

Key,

...

resident orders;

Re: Link Table issue

petter — Mon, 24 Aug 2015 07:21:00 GMT

One trade can be one or more orders. But any particular order belongs to only one trade. Right?

You don't need a link table when you have a one-to-many association as it seems like you will have here. But if you really want you can create one - although it is seldom advisable to create link tables that you can avoid when using QlikView.

Is your problem how to visualize it in a Pivot or Straight Table?

Re: Link Table issue

sifatnabil — Mon, 24 Aug 2015 07:26:58 GMT

Hi petter.skjolden‌, the relationship is many (Orders) to one (Trades) yes. The issue lies in the fact that if an OrderID exists in Trades, but doesn't exist in Orders, it's completely excluded from the loaded data. The link table is only showing Order IDs which are common to both.

Does it mean I should do a left join on Key (with Trades being on left) in order to show all Order IDs?