Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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?
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?