How to fetch the corresponding value from the data which have minimum, maximum and current value for number?

[srujana96]

i have the below data,

dc_number	argosweekstart	total_forecast
610	2022-10-23	23534.000003657507
610	2022-05-22	457659.9999990086
610	2022-06-19	457026.96672087134
610	2022-06-12	499736.9999989038

i have fetched the below table which have maximum minimum and current data corresponding to the number from the below query
index="index" 
|stats min(total_forecast) as Minimum max(total_forecast) as Maximum latest(total_forecast) as Current by dc_number
|table dc_number week_min Minimum week_max Maximum week_cur Current

dc_number	week_min	Minimum	week_max	Maximum	week_cur	Current
610		23534.000003657507		499736.999998903800		23534.000003657507

but i am expecting the below output with corresponding week value from the first table. that means, for week_min it should pick it's corresponding value from week from the minimum value and same from maximum and current. below is the expected output.

dc_number	week_min	Minimum	week_max	Maximum	week_cur	Current
610	2022-10-23	23534.000003657507	2022-06-12	499736.999998903800	2022-10-23	23534.000003657507

[richgalloway · Accepted solution]

The stats command is a transforming one.  That means the only fields in the output are those mentioned in the command - in this case Minimum, Maximum, Current and dc_number.  You can use eventstats to calculate Minimum, Maximum, and Current without losing other fields.  Then you can do some manipulations to get the final result.

| makeresults | eval _raw="dc_number	argosweekstart	total_forecast
610	2022-05-22	457659.9999990086
610	2022-10-23	23534.000003657507
610	2022-06-19	457026.96672087134
610	2022-06-12	499736.9999989038"
| multikv forceheader=1
```Above just sets up test data```
```Compute min, max, and current values```
| eventstats min(total_forecast) as Minimum, max(total_forecast) as Maximum, 
latest(total_forecast) as Current by dc_number
```Find the week number that matches min, max, and current```
| eval week_cur=if(total_forecast=Current, argosweekstart, null()), week_max=if(total_forecast==Maximum, argosweekstart, null()), week_min=if(total_forecast=Minimum, argosweekstart, null())
| table dc_number week_min Minimum week_max Maximum week_cur Current
```Merge the results into a single row for each dc```
| stats values(*) as * by dc_number

[somesoni2]

Give this a try

index="YourIndex"
| eventstats min(total_forecast) as min max(total_forecast) as max latest(total_forecast) as current by dc_number
|stats values(eval(if(total_forecast=min,argosweekstart,null()))) AS week_min min(total_forecast) AS Minimum  values(eval(if(total_forecast=max,argosweekstart,null()))) AS week_max max(total_forecast) AS Maximum values(eval(if(total_forecast=current,argosweekstart,null()))) AS week_cur latest(total_forecast) AS Current by dc_number 

[srujana96]

Thank you @somesoni2 . it worked!!

[richgalloway]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.