How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to run a search which looks at a value in column A (detail.overall), and if they contain specific values, then insert a specific value into the "Rating" column or zero.

So if the detail.overall column is:

Very Satisfied - Insert the value of 100 in the "Ratings" Column
Satisfied - Insert the value of 75 in the "Ratings" Column
Neither - Insert the value of 50 in the "Ratings" Column
Dissatisfied - Insert the value of 25 in the "Ratings" Column
Very Dissatisfied - Insert the value of 0 in the "Ratings" Column

If none of the values are found, I'd like to insert a zero.

This is the search I've put together so far:

index=main auditSource=frontend auditType=Survey detail.overall!="None" | replace frontend with "Overall Satisfaction" | replace 1 with "Very Satisfied", 2 with "Satisfied", 3 with "Neither", 4 with "Dissatisfied", 5 with "Very Dissatisfied" | chart count by detail.overall | addcoltotals|fillnull value="Total" count | eval Rating = if(match(detail.overall, "5"), "100", "0")

There are two problems I have:

[] Using the value "Very Satisfied", I'm unable to insert the value of 100 in the "Ratings" column
[] I'm unsure how to create a nested if statement to take into all of the text values.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to achieve this.

Many thanks and kind regards

Chris

[HeinzWaescher · Accepted solution]

I think you need the case command here:

...
| eval rating=case(detail.overall="Very Satisfied", 100, detail.overall="Satisfied", 75 , detail.overall="Neither", 50, detail.overall="Dissatisfied", 25, detail.overall="Very Dissatisfied", 0 , 1=1, 0)

[IRHM73]

Hi @HeinzWaescher, thank you very much for taking the time to reply to my post.

I've tried your solution but unfortunately this doesn't work. I'm not sure whether this is because the solution is the opposite way around to my original post.

I'd like if possible to insert the value into the Ratings column if:

The detail.overall column value:

Very Satisfied - Insert the value of 100 in the "Ratings" Column
Satisfied - Insert the value of 75 in the "Ratings" Column
Neither - Insert the value of 50 in the "Ratings" Column
Dissatisfied - Insert the value of 25 in the "Ratings" Column
Very Dissatisfied - Insert the value of 0 in the "Ratings" Column

Many thanks and kind regards

Chris

[HeinzWaescher]

I mixed it up. Try my updated answer.

[IRHM73]

Hi, thank you very much for coming back to me with this.

Unfortunately though, although the query does run, it is not inserting the values into the column and all rows remain zero.

Many thanks and kind regards

Chris

[HeinzWaescher]

Hm, perhaps it does not like the dot in the fieldname?
You could try out to pipe this before the eval command

| rename detail.overall -> detail_overall
| eval rating=case(detail_overall="Very Satisfied", 100, detail_overall="Satisfied", 75 , detail_overall="Neither", 50, detail_overall="Dissatisfied", 25, detail_overall="Very Dissatisfied", 0 , 1=1, 0)

[IRHM73]

Hi, that's really very interesting about the '.' It now works perfectly!

Many thanks and kind regards

Chris