Joining data from different types of records

You can use EQL to join data from different types of records.

Use lookups against unfiltered records to avoid eliminating all records of a secondary type when navigation refinements are selected from an attribute only associated with the primary record type.

In the following example, the following types of records are joined:

Record type 1

RecordType: Review
Rating: 4
ProductId: Drill-X15
Text: This is a great product...

Record type 2

RecordType: Transaction
SalesAmount: 49.99
ProductId: Drill-X15
...

The query is:

DEFINE Ratings AS SELECT
    AVG(Rating) AS AvScore
FROM Reviews.UNFILTERED
WHERE RecordType = 'Review'
GROUP BY ProductId ;

RETURN TopProducts AS SELECT
   SUM(SalesAmount) AS TotalSales,
   Ratings[ProductId].AvScore AS AvScore
FROM Ratings
WHERE RecordType = 'Transaction'
GROUP BY ProductId
ORDER BY TotalSales DESC
PAGE(0,10)