CASE WHEN
or multiplying a value). They can also be complex computations that span multiple related entities and aggregations.
lineitems
entity (which represents lines of an order), there is an attribute that is called extendedprice
. It represents the price not accounting for discount. You might want to have an attribute that reflects the actual price.
The definition of price
is:
Build the new calculated attribute
lineitems
entity, and press “+Attribute”. Call it price
, enter
the SQL calculation formula above and save it.Check it with Preview Data
orders
entity has a value for each order.
If you want to combine it with attributes of other entities, it can use any attribute from the customers
entity, but not from the lineitem
entity:
See the relationship graph
customers
entity is related to orders
entity with a lower granularity (a many:1 relationship). Any attribute in orders
may refer to any attribute in customers
. Technically, the row per order can be extended with columns coming from its customer.lineitem
entity is related to the orders
entity with a higher granularity (a 1:many relationship). So the is no single lineitem per order to choose from for the calculation, so using it would result in an error.lineitems
for an order: it possible to create an attribute per order that counts how many lineitems it had. See examples below.
parts
and the lineitems
entity: every lineitem has an associated part that can be used in the calculation.
lineitems
has exactly one associated part from parts
this calculation is possible. If the relation was reversed (a line item would have multiple possible parts), it would result in an error as it would not be possible to choose a part.extendedprice
attribute of lineitems
. We can recreate the same calculation.
Build the new calculated attribute
lineitems
entity, and press “+Attribute”.
Call it extendedprice_calculated
, and enter the SQL calculation lineitems.quantity * parts.retailprice
and save it.Check it with a Dynamic Dataset
lineitems.extendedprice
(the original from TPC-H)lineitems.extendedprice_calculated
(the new one you just built)Update the `price` attribute to use it
lineitems
entity, and edit the price
attribute.
Change its calculation frompromotion_part_types
that has a list of every promoted part type.
is_promotion
attribute for a part would be TRUE is it matches a value from that table.
This is called a lookup attribute:
WHERE IN (SELECT ...)
over a
subquery of potential attribute values.customer
can’t refer to a single order, but it can look at any aggregation over them like a count.
See the relationship graph
SUM
or a COUNT
)totalprice
attribute of orders
TPC-H sample data. We can recreate the same calculation as a calculated field.
Note this example uses the price
calculated attribute defined in the previous section.
Build the total price attribute
orders
entity, and press “+Attribute”.
Call it totalprice_calculated
, enter the SQL calculation formula:Build the order size classification attribute
orders
entity, and press “+Attribute”.
Call it order_size_classification
, enter the SQL calculation formula:Check it with a Dynamic Dataset
orders.order_size_classification
attributes and a single metric COUNT(*) as count
. Preview its data to see how many orders of each size classification there are.
It should be something like:orders.order_size_classification | count |
---|---|
big | 228,149 |
small | 86,560 |
regular | 1,185,291 |
F
(fulfilled) then status of order is F
O
(ordered) then status of order is O
P
(partially fulfilled)lineitems.count
that is the count of lineitems, combined with a filter:
FILTER
qualifier like her it can reduced to aggregating over
a selection of related rows to the attribute instead of them all. Check out
metrics for more.orderstatus
attribute of orders
TPC-H sample data.
entity
: Name of entity the attribute belongs to
name
: Name of attribute
display_name
, owner
, description
, labels
, folder
, hidden
: Metadata
datatype
: Data type of attribute, as exposed to JDBC (string
is the default, if not specified).
Possible values are:
bool
- a boolean value.number
- an integer numberfloat
- a floating point numberdate
- a date valuetimestamp
- a timestamp valuestring
- a string value, used also for any other type of data (like array, JSON, etc.)timegrain
: Time grain of the attribute, if it is a date/time attribute.
This can be used in time-based aggregations.
Possible values are:
microsecond
, millisecond
, second
, minute
, hour
, day
, week
, month
, quarter
, year
.
sql
: SQL statement of the calculated attribute
metadata
: Additional metadata for the attribute
(see examples for AI,
Tableau,
Power BI,
Lightdash and
ThoughtSpot)