CASE statement not returning required output

[schou87]

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.

[ITWhisperer]

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

[schou87]

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

[ITWhisperer]

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.

[kamlesh_vaghela]

@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.