Joining data from different types of records

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

Use lookups against AllBaseRecords 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

...

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

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