How to implement partition by function in LookML

Looker Q&A Looker
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

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 help.looker.com any time.

Best,
Andrew

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.

[[Analytic Block] Sequencing Transactions](https://discourse.looker.com/t/analytic-block-sequencing-transactions/265) Blocks

##About This Block Nearly all businesses want customers to come back. It’s often much easier to increase your profitability by selling more to your existing customers than to acquire new customers. An interesting field to create for this is ORDERS Order Sequence Number. ORDERS means its an attribute of an order. Order Sequence Number means that we are computing an index for this order relative to the other orders a user has placed. For a buyer, their first order will be numbered ‘1’, their sec…

1 Like
4

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