eval status=if ("inputlookup line x<70% and x+1>50%) Boolean While Loop?

[wblewis]

Hello,

I am looking for some help on status evaluation. What I am trying to do is create a eval column where you either have true of false based on FROZEN_PCT or REFRIGERATOR_PCT

Status = False  (This occurs when the capacity has reached 70% and has not decreased to 50% yet) The value will not increase after it reached 70% or greater. Highlighted in red

Status = True (This occurs if the value is less than .7 and lowered  has below .5 after going false) Everything else not highlighted

Let me know if you can help!

UPDATED_TS	Date	Hour	TIME	FROZEN_PCT	REFRIGERATOR_PCT
6/10/20 19:44	6/10/2020	19	19:44	4.70%	33.63%
6/10/20 19:35	6/10/2020	19	19:35	8.77%	33.17%
6/10/20 19:35	6/10/2020	19	19:35	8.77%	37.43%
6/10/20 19:25	6/10/2020	19	19:25	8.77%	37.66%
6/10/20 18:44	6/10/2020	18	18:44	8.77%	41.49%
6/10/20 18:43	6/10/2020	18	18:43	4.70%	37.66%
6/10/20 18:39	6/10/2020	18	18:39	4.70%	36.58%
6/10/20 18:38	6/10/2020	18	18:38	4.70%	37.28%
6/10/20 18:23	6/10/2020	18	18:23	21.44%	41.55%
6/10/20 18:22	6/10/2020	18	18:22	21.44%	49.19%
6/10/20 17:47	6/10/2020	17	17:47	21.44%	49.19%
6/10/20 17:42	6/10/2020	17	17:42	21.44%	58.00%
6/10/20 17:27	6/10/2020	17	17:27	21.44%	59.22%
6/10/20 17:25	6/10/2020	17	17:25	21.44%	61.80%
6/10/20 16:54	6/10/2020	16	16:54	21.44%	61.80%
6/10/20 16:54	6/10/2020	16	16:54	21.44%	62.29%
6/10/20 16:52	6/10/2020	16	16:52	21.44%	63.95%
6/10/20 16:50	6/10/2020	16	16:50	21.44%	69.11%
6/10/20 16:45	6/10/2020	16	16:45	21.44%	73.59%
6/10/20 16:37	6/10/2020	16	16:37	16.74%	67.68%
6/10/20 16:33	6/10/2020	16	16:33	16.74%	55.12%
6/10/20 16:12	6/10/2020	16	16:12	0.00%	51.22%
6/10/20 15:55	6/10/2020	15	15:55	0.00%	59.01%
6/10/20 15:39	6/10/2020	15	15:39	0.00%	50.19%
6/10/20 15:36	6/10/2020	15	15:36	0.00%	51.29%
6/10/20 15:30	6/10/2020	15	15:30	0.00%	49.64%
6/10/20 14:59	6/10/2020	14	14:59	0.00%	49.27%
6/10/20 14:59	6/10/2020	14	14:59	0.00%	46.02%
6/10/20 14:53	6/10/2020	14	14:53	0.00%	54.06%
6/10/20 14:18	6/10/2020	14	14:18	0.00%	46.43%
6/10/20 14:00	6/10/2020	14	14:00	0.00%	38.64%
6/10/20 13:44	6/10/2020	13	13:44	0.00%	38.64%
6/10/20 13:34	6/10/2020	13	13:34	2.56%	40.42%
6/10/20 12:25	6/10/2020	12	12:25	2.56%	39.32%
6/10/20 12:01	6/10/2020	12	12:01	2.56%	38.61%
6/10/20 11:43	6/10/2020	11	11:43	2.56%	38.61%

[bowesmana]

Here's a run anywhere example with your data

| makeresults
| eval _raw=
"UPDATED_TS	Date	Hour	TIME	FROZEN_PCT	REFRIGERATOR_PCT
6/10/20 19:44	6/10/2020	19	19:44	4.70%	33.63%
6/10/20 19:35	6/10/2020	19	19:35	8.77%	33.17%
6/10/20 19:35	6/10/2020	19	19:35	8.77%	37.43%
6/10/20 19:25	6/10/2020	19	19:25	8.77%	37.66%
6/10/20 18:44	6/10/2020	18	18:44	8.77%	41.49%
6/10/20 18:43	6/10/2020	18	18:43	4.70%	37.66%
6/10/20 18:39	6/10/2020	18	18:39	4.70%	36.58%
6/10/20 18:38	6/10/2020	18	18:38	4.70%	37.28%
6/10/20 18:23	6/10/2020	18	18:23	21.44%	41.55%
6/10/20 18:22	6/10/2020	18	18:22	21.44%	49.19%
6/10/20 17:47	6/10/2020	17	17:47	21.44%	49.19%
6/10/20 17:42	6/10/2020	17	17:42	21.44%	58.00%
6/10/20 17:27	6/10/2020	17	17:27	21.44%	59.22%
6/10/20 17:25	6/10/2020	17	17:25	21.44%	61.80%
6/10/20 16:54	6/10/2020	16	16:54	21.44%	61.80%
6/10/20 16:54	6/10/2020	16	16:54	21.44%	62.29%
6/10/20 16:52	6/10/2020	16	16:52	21.44%	63.95%
6/10/20 16:50	6/10/2020	16	16:50	21.44%	69.11%
6/10/20 16:45	6/10/2020	16	16:45	21.44%	73.59%
6/10/20 16:37	6/10/2020	16	16:37	16.74%	67.68%
6/10/20 16:33	6/10/2020	16	16:33	16.74%	55.12%
6/10/20 16:12	6/10/2020	16	16:12	0.00%	51.22%
6/10/20 15:55	6/10/2020	15	15:55	0.00%	59.01%
6/10/20 15:39	6/10/2020	15	15:39	0.00%	50.19%
6/10/20 15:36	6/10/2020	15	15:36	0.00%	51.29%
6/10/20 15:30	6/10/2020	15	15:30	0.00%	49.64%
6/10/20 14:59	6/10/2020	14	14:59	0.00%	49.27%
6/10/20 14:59	6/10/2020	14	14:59	0.00%	46.02%
6/10/20 14:53	6/10/2020	14	14:53	0.00%	54.06%
6/10/20 14:18	6/10/2020	14	14:18	0.00%	46.43%
6/10/20 14:00	6/10/2020	14	14:00	0.00%	38.64%
6/10/20 13:44	6/10/2020	13	13:44	0.00%	38.64%
6/10/20 13:34	6/10/2020	13	13:34	2.56%	40.42%
6/10/20 12:25	6/10/2020	12	12:25	2.56%	39.32%
6/10/20 12:01	6/10/2020	12	12:01	2.56%	38.61%
6/10/20 11:43	6/10/2020	11	11:43	2.56%	38.61%"
| multikv forceheader=1
| eval _time=strptime(UPDATED_TS,"%m/%d/%y %H:%M")
| fields - _raw linecount
| eval x="Your needed code from here"
| reverse
| eval REFRIGERATOR_PCT=tonumber(replace(REFRIGERATOR_PCT, "%",""))
| eval Status=case(REFRIGERATOR_PCT>70,"true",REFRIGERATOR_PCT<50,"false")
| filldown Status
| fields - x

Just the last few lines you need from the reverse. Does this fit for what you are trying to achieve.

Note that this relies on the data being fed in from oldest to newest, so that it can determine the first breach of the 70% threshold. After that it simple sets true or false based on >70 or <50 and then filldown to fill in the gaps from the previous value.

Hope this helps

[wblewis]

Yes, this is exactly what I need! If instead of inserting all of the data like you did into the search if I had a lookup file called "sample.csv" how would that change the search? 

[bowesmana · Accepted solution]

so if that data is in a lookup file then the following is all you need

| inputlookup lookup.csv
| reverse
| eval REFRIGERATOR_PCT=tonumber(replace(REFRIGERATOR_PCT, "%",""))
| eval Status=case(REFRIGERATOR_PCT>70,"true",REFRIGERATOR_PCT<50,"false")
| filldown Status

Note: I used a lookup with comma delimited data

[wblewis]

Would you also be able to help me understand the duration when true? 

I.E how long did it take for it to reduce from above 70 to below 50

Thanks!

[bowesmana]

This will give you the duration from the first item >70 until the time of the event PRIOR to the one that is below 50

| inputlookup lookup.csv
| reverse
| eval REFRIGERATOR_PCT=tonumber(replace(REFRIGERATOR_PCT, "%",""))
| eval Status=case(REFRIGERATOR_PCT>70,"true",REFRIGERATOR_PCT<50,"false")
| eval _time=strptime(UPDATED_TS,"%m/%d/%y %H:%M")
| delta _time as td
| filldown Status
| streamstats reset_on_change=t sum(td) as Duration earliest(td) as First by Status
| eval Duration=Duration-First
| fields - First td

it will not calculate to include the one after it has gone below. You can decide if this is good enough