topic Re: How to fetch the corresponding value from the data which have minimum, maximum and current value for number? in Splunk Search

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

srujana96 — Tue, 17 May 2022 14:51:01 GMT

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

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

somesoni2 — Tue, 17 May 2022 16:07:03 GMT

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

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

richgalloway — Tue, 17 May 2022 21:19:27 GMT

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

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

srujana96 — Wed, 18 May 2022 06:41:37 GMT

Thank you @somesoni2 . it worked!!

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

richgalloway — Wed, 18 May 2022 12:42:45 GMT

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