AnsweredAssumed Answered

How to split records inside an aggregation (based on a condition)?

Question asked by deepa.govind@integration.works on Dec 20, 2017
Latest reply on Jan 10, 2018 by deepa.govind@integration.works

I have this requirement now to break the data into different bands when the AR value range is >5K.

 

YearMakeModelARGR
2010SubaruLegacy12450 14400
2010SubaruLegacy14300 16400
2010SubaruLegacy14350 16450
2010SubaruLegacy15700 17950
2010SubaruLegacy16350 18650
2010SubaruLegacy17800 20200
2010SubaruLegacy22600 25400
2010SubaruLegacy22600 25400

 

For the above example, the aggregated output is like below (I had to split on year/make/model and then use the Min(AR) and Max(GR) to arrive at this record). 

 

Existing output:

YearMakeModelAR (Min)GR (Max)
2010SubaruLegacy12450 25400

 

But now the requirement has been changed to have them split into different bands(whenever the AR value diff by 5k) like below. I would have already arrived at Min -Max and combine into a single record by this time (even before checking the 5K difference in AR)

 

 

MinMax
Starting position for the 5k tolerance12450 18650
Suggest the we always
default to the lowest qualifying band

18550

(prev Max -100)

 25400

 

Expected Output: 

YearMakeModelAR(Min)GR(Max)
2010SubaruLegacy12450 18650
2010SubaruLegacy1855025400

 

Any help is appreciated.

 

Cheers

Deepa

Outcomes