topic Re: CASE statement not returning required output in Dashboards & Visualizations

CASE statement not returning required output

schou87 — Mon, 07 Jun 2021 09:09:32 GMT

Hi,

I need my output as below

Date Greater than 12Hrs Greater than 24Hrs Greater than 48Hrs Greater than 72Hrs

Below is my query

base_query | eval Final_TIME=CASE(TOTAL_TIME>12, "Greater than 12Hrs", TOTAL_TIME>24, "Greater than 24Hrs", TOTAL_TIME>48, "Greater than 48Hrs", TOTAL_TIME>72, "Greater than 72Hrs") | CHART COUNT OVER Date BY Final_TIME | sort - Date | eval Date=strftime(strptime(Date,"%Y-%m-%d"),"%m-%d-%Y") | head 7 | addtotals

My query is only returning

Date Greater than 12Hrs NULL

Please help.

Re: CASE statement not returning required output

ITWhisperer — Mon, 07 Jun 2021 09:32:20 GMT

Case is evaluated left to right so you could try reordering the comparisons

base_query | eval Final_TIME=CASE(TOTAL_TIME>72, "Greater than 72Hrs", TOTAL_TIME>48, "Greater than 48Hrs", TOTAL_TIME>24, "Greater than 24Hrs", TOTAL_TIME>12, "Greater than 12Hrs") | CHART COUNT OVER Date BY Final_TIME | sort - Date | eval Date=strftime(strptime(Date,"%Y-%m-%d"),"%m-%d-%Y") | head 7 | addtotals

Re: CASE statement not returning required output

schou87 — Tue, 08 Jun 2021 05:51:35 GMT

Hi @ITWhisperer

I am still getting the NULL field and its giving me wrong data. I don't see the "Greater than 72Hrs" field.

Date Greater than 12Hrs Greater than 24Hrs Greater than 48Hrs NULL Total

Re: CASE statement not returning required output

kamlesh_vaghela — Tue, 08 Jun 2021 06:17:26 GMT

@schou87

Can you please try this?

base_query TOTAL_TIME=*

And rest of the search provided by @ITWhisperer .

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: CASE statement not returning required output

ITWhisperer — Tue, 08 Jun 2021 06:29:02 GMT

The NULL is there because you didn't include a value for TOTAL_TIME <= 12, or else in the case function.

base_query | eval Final_TIME=CASE(TOTAL_TIME>72, "Greater than 72Hrs", TOTAL_TIME>48, "Greater than 48Hrs", TOTAL_TIME>24, "Greater than 24Hrs", TOTAL_TIME>12, "Greater than 12Hrs", 1==1, "Less than 12Hrs") | CHART COUNT OVER Date BY Final_TIME | sort - Date | eval Date=strftime(strptime(Date,"%Y-%m-%d"),"%m-%d-%Y") | head 7 | addtotals

Apart from that, the counts will be based on the value of TOTAL_TIME. If the counts are wrong, check that TOTAL_TIME is as you expect it to be. Try running the search on a smaller data set so you can check it.