The view definition EQL defines the content of the view. When defining a view, you need to keep the following guidelines in mind. For complete details on EQL syntax, see the EQL Reference.
View DEFINE and SELECT statements
A view definition may contain multiple DEFINE
and SELECT
statements.
The last statement must be a DEFINE
statement that contains all of the view attributes. The name of that DEFINE
statement is used as the view key, and cannot be changed. If you try to change the view key for an existing view, the corresponding components will no longer work.
View RETURN statements
Although the EQL Reference uses RETURN statements in its code examples, you should not add RETURN statements to any views you create in Studio. Studio automatically wraps your EQL with a RETURN statement.
You can copy EQL examples from the EQL Reference into a view and then delete the RETURN statement. This way, Studio wraps the EQL with an implicit RETURN and the example works correctly. If you explicitly add the RETURN the view fails.
Providing the source view for the view attributes
For all custom view definitions, you must use the FROM
parameter to provide the original source of the view attributes.
For example, if you create a copy of a base view, you will see that the view definition includes the view key for that base view.
DEFINE Customers as SELECT
ARB(Customer_Name) as Customer_Name,
ARB (Customer_Address) as Customer_Address,
ARB(Cities) as Cities,
ARB(States) as States,
ARB(CustomerZip) as CustomerZip,
ARB(LatLong) as LatLong,
ARB(Business_Types) as Business_Types,
ARB(CustomerAgreedDaysCredit) as CustomerAgreedDaysCredit,
ARB(Credit_Rating) as Credit_Rating,
ARB(CustomerDiscount) as CustomerDiscount,
SUM(Number_of_Cases_Sold) as TotalCustomerCases,
CountDistinct(Transaction_Id) as TotalCustomerTransactions,
SUM(GrossDollars) as TotalCustomerGross,
SUM(MarginDollars) as TotalCustomerMargin,
SET(Shipping_Companies) as ShippingCompaniesUsed
FROM "wine-sales"
GROUP BY Customer_Id
When identifying the source view, make sure to use the view key, and not the view display name.
Also, when referring to another view, you can only refer to the final DEFINE
statement that identifies the attributes for other view. You cannot refer to any intermediate DEFINE
or SELECT
statements in that view definition.
Grouping a view
All custom views should have at least one grouping (GROUP BY
) attribute.
Studio uses the GROUP BY
attributes as the identifying attributes for the view records.
If there are no GROUP BY
attributes, then there are no identifying attributes, and the view cannot be used for Record Details or Compare. On component edit views, the Record Details and Compare options are then automatically disabled.
Using multi-value attributes for grouping
GROUP BY
.GROUP BY MEMBERS
. The syntax for GROUP BY MEMBERS
is slightly different from GROUP BY
:
GROUP BY MEMBERS(attributeName) as attributeName
For example, the following data shows available product colors, and the number of outlets that carry each product:
ItemColors | AvailableOutlets |
---|---|
Red, Blue | 3 |
Red | 5 |
Red, Blue, White | 6 |
Red, Blue, White | 4 |
Red, Blue | 1 |
DEFINE NewView as SELECT TotalOutlets as sum(AvailableOutlets) FROM Sales GROUP BY ItemColors
, then the result is:
ItemColors | TotalOutlets |
---|---|
Red, Blue | 4 |
Red | 5 |
Red, Blue, White | 10 |
DEFINE NewView as SELECT TotalOutlets as SUM(AvailableOutlets) FROM Sales GROUP BY MEMBERS(ItemColors) as ItemColors
, then the result is:
ItemColors | TotalOutlets |
---|---|
Red | 19 |
Blue | 14 |
White | 10 |
Providing aggregation methods when grouping view attributes
When defining a view, if you are using grouping, then you must provide an aggregation method for all of the attributes in the view, except for the grouping attributes.
If you using aggregation to derive attributes from the physical attributes, then you can use the applicable aggregation methods for the attribute data type. See Aggregation methods and the data types that can use them.
ARB
aggregation method. For example:
SELECT ARB(Region) as Region
Note that if there are different values for an attribute within the grouping, Studio chooses a single, arbitrary value.
ARB
aggregation method. For example:
SELECT ARB(ItemColors) as ItemColors
SET_UNIONS
aggregation method. For example:
SELECT SET_UNIONS(ItemColors) as ItemColors
If you are not using grouping, then you do not need an aggregation method. For example:
SELECT ProductName as ProductName
In this case, you do not need to specify the name, so you could also use:
SELECT ProductName
Naming view attributes
For example, for a "Region" attribute, when you add the attribute to a view, define it as "Region".
SELECT ARB(Region) AS Region
Do not define the attribute under a different name, such as:
SELECT ARB(Region) AS RegionList
End users can only refine by attributes that are present in the physical data. If you alias an attribute, then end users cannot use it for refinement.
For example, for an attribute that averages the values of the "Sales" attribute, do not define the new attribute as:
SELECT AVG(Sales) as Sales
Instead, use something like:
SELECT AVG(Sales) as AvgSales
This is also to prevent end users from trying to refine by an attribute that is not in the physical data.
Sample view definition
Here is an example of an EQL query for generating a list of products, including price, available colors, and sales numbers, from a single data set consisting of individual transactions.
DEFINE Products AS SELECT ARB(ProductSubcategoryName) AS ProductSubcategoryName, ARB(ProductCategoryName) AS ProductCategoryName, ARB(Description) AS Description, SET_UNIONS(AvailableColors) AS AvailableColors, AVG("FactSales_SalesAmount") AS AvgSales, SUM("FactSales_SalesAmount") AS SalesSum, AVG(StandardCost) AS AvgStandardCost, AVG(ListPrice) AS AvgListPrice, (AvgListPrice - AvgStandardCost) AS Profit FROM Sales GROUP BY ProductName