# Transforms combining pivot & latest
**URL:** https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193
**Category:** Elasticsearch
**Tags:** transforms
**Created:** [December 14, 2022, 8:14am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193 "2022-12-14T08:14:31Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 14, 2022, 8:14am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/1 "2022-12-14T08:14:31Z")
Hello,
I have a case where i pivot data based on :
- @timestamp (interval of 1 day)
- Entity (let say customer\_account)
A customer\_account can order items any time, so raw data are like :
- order\_id
- @timestamp
- product\_id
- product\_name
- quantity
- price
- ... etc
I need a final index where i can track on a daily basis the behavior of customers, so my final indices looks like :
- customer\_account
- @timestamp (yyyy-MM-dd)
- number\_of\_orders
- total\_prices
- first\_order\_of\_customer
My issue is with `first_order_of_customer` i can only have the `first_order_of_customer` in each day
Is there any way i can include the real `first_order_of_customer` in each day aggregated data ?
Example : customer 1000087 ordered for the first time in 2022-12-03
I need to have first\_order\_of\_customer = 2022-12-03 in aggregated data of day 2022-12-13 (if customer ordered something in 2022-12-13)
Thanks for support
---
### Author:  [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)
#### Post date: [December 15, 2022, 7:25am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/2 "2022-12-15T07:25:41Z")
With the `date_histogram` in `group_by` you cut off the necessary documents.
I can imagine having another transform just for updating a `first_order_of_customer` index and use enrich (using an ingest pipeline on `dest`) in your daily transform. Instead of enrich you could also have a 3rd transform that combines both, in this case however enrich seems simpler and sufficient.
---
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 15, 2022, 7:43am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/3 "2022-12-15T07:43:29Z")
Thanks @Hendrik_Muhs for your feedback
My only issue with enrich pipeline is that it require to execute the enrich strategy each time the enrich index is updated, in my case my enrich index (`customer_account` + `first_order_of_customer`) is dynamic and not static
---
### Author:  [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)
#### Post date: [December 15, 2022, 9:44am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/4 "2022-12-15T09:44:12Z")
I was thinking that the `first_order_of_customer` index might change rarely and it might be ok to re-create this lookup index daily.
In the transform where you have enrich, you could fallback to the first order in the bucket if enrich doesn't match anything as that indicates a new customer.
Anyway, the only other alternative I see is using another transform that merges both the `first_order_of_customer` and your `daily_orders` index into one.
---
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 15, 2022, 10:13am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/5 "2022-12-15T10:13:00Z")
> [@Hendrik\_Muhs](#):
>
> Anyway, the only other alternative I see is using another transform that merges both the `first_order_of_customer` and your `daily_orders` index into one.
With this scenario, the only thing i can do is to have cumulated & aggregated data per customer in a 3rd index, i will loose the daily based analysis
---
### Author:  [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)
#### Post date: [December 15, 2022, 10:35am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/6 "2022-12-15T10:35:17Z")
> [@ylasri](#):
>
> With this scenario, the only thing i can do is to have cumulated & aggregated data per customer in a 3rd index, i will loose the daily based analysis
Got you, you are right, it does not work with a 3rd transform.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 12, 2023, 10:35am UTC](https://discuss.elastic.co/t/transforms-combining-pivot-latest/321193/7 "2023-01-12T10:35:31Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.