How to implement partition by function in LookML


(Miao) #1

There is a table, for example
Product / Order / Price
A / 1 / $6
A / 2 / $11
A / 3 / $7
B / 4 / $8
B / 5 / $12
B / 6 / $2

I want to return the first order for each product
Product / Order / Price
A / 1 / $6
B / 4 / $8


(Andrew Rose) #2

Hi,

Looker support here, thanks for reaching out. Generally, to implement “partition by” in Looker, you can create a derived table that uses window functions: Using Window Functions in Looker

This Discourse thread has some more discussion about implementing “partition by” in Looker: Implementation of Partition By in Looker

One other option outlined in this article is to use table calculations: https://docs.looker.com/exploring-data/using-table-calculations. For example, using the sample you provided above, we could use a table calculation to get the minimum order number for each product.

Let me know if you have any additional questions about this subject, or feel free to send us an email at support@looker.com any time.

Best,
Andrew


(lloyd tabb) #3

Hi there.

Probably the best general solution to this problem is to build a dimension on orders that is the sequence number for the transaction. The reason this is useful is that once sequence number is added to the model, you can look at the first, second, last or Nth transaction. Or make comparisons between transactions, right from the explore interface.

At the end of the day, you end up with a new dimesion, ‘Order Sequence Number’. In your case, you would just filter the data where that number is 1.

Here is an article about how to implement that in LookML.


(Miao) #4

Thanks, Andrew and lloyd. I will build the sequence number dimension.