Faster and simpler than a link table in Qlik Sense?

[Daniel_Moore]

I often get the opportunity to teach new data modelers how a link table works. Users get frustrated because the conversation gets too complicated for trying to accommodate differing levels of detail. The student says 'but all I am trying to do is link quotas to orders' surely there must be an easier way to compare quotas and results? They are looking for a simple chart comparing quota and achievement: 

The key is to create a composite key in the quota table and add aggregated sales results to the Quota table. 

For those students I have put together a solution that uses the basic fundamental techniques taught in Qlik's Data Modeling Qlik Sense Class. Namely,  aggregating data, preceding loads, outer joins, and composite keys. The technique has no chance of circular references nor synthetic keys- and it does this using a star schema data model. 

I've attached the qvf - and here is what the script looks like: 

SalesPerson:
LOAD
EmpID,
Name
FROM [lib://Data/LinkTable.xlsx](ooxml, embedded labels, table is Salesperson);

Quota:
LOAD
EmpID &'|'& Period as QuotaPeriod ,
Target * 1000000 as Target // used this because quotas were in millions
FROM [lib://Data/LinkTable.xlsx](ooxml, embedded labels, table is Quota);

Orders:
LOAD *, // Preceding load could be removed if quarter was in the Order table - usually it isn't
EmpID &'|Q'& Quarter &'-'& Right(Year,2) as QuotaPeriod; // This composite key allows us to track tie the order to quota achievement
LOAD
OrderID,
OrderDate,
Ceil(Month(OrderDate) / 3) as Quarter, // Quarter normally not in orders
EmpID,
Year,
Sales
FROM [lib://Data/LinkTable.xlsx](ooxml, embedded labels, table is Orders);

// Make sure you drop fields you no longer need to avoid cicular references- date fields will be in the master calendar
Drop fields Quarter, Year From Orders;

OUTER JOIN (Quota) //Outer join ensures that sales are captured even if there are no quotas.
LOAD
QuotaPeriod,
Sum(Sales) as SalesPerformance
RESIDENT Orders
Group by QuotaPeriod; // group by is being used here to roll up sales to quarterly basis- called aggregating data

You could do the same thing with a link table but for performance I'd think you'd want to join the link table with the budget table anyway. What do you think of this approach? 

[marcus_sommer]

In general there are three different approaches to match the data. One would be to join respectively to associate the tables. Quite often there are missing key-values on both sides and/or a different granularity of the data and/or the relation between them isn't 1:1 or 1:n - so that there are multiple load-steps needed to adjust the data appropriate and so this approach is usually the most complex one.

The link-table approach is a kind of shortcut to the above mentioned approach and will reduce the complexity and the number of working-steps - but often with significantly disadvantages in regard to the UI performance.

The most easy way to match such tables is often just to concatenate them. In most cases it avoids the problems with missing keys and adverse relations and the resulting (more or less asynchron) table with NULL's in various columns performs usually very well. Therefore it's my suggestion in regard of simplicity and performance.

- Marcus