How to use eval to rename a variable unter certain conditions

[zoe]

I have under each orderNr five different weights.

__________________________

Weight: 0.898, WeightTypeId: 1, OrderNr: 8478

Weight: 0.094, WeightTypeId: 2, OrderNr: 8478

Weight: 7.45, WeightTypeId: 3, OrderNr: 8478

Weight: 0.0, WeightTypeId: 4, OrderNr: 8478

Weight: 7.45, WeightTypeId: 5, OrderNr: 8478

...............................

___________________________________

I would like to calculate the total weight and the yields, which is calculated like this: 
Total:  Weight(WeightTypeID1) + Weight(WeightTypeID2) +Weight(WeightTypeID4) +Weight(WeightTypeID5)
Yield1 = Weight (WeightTypeId 1)/ Total
Yield2= Weight(WeightTypeID3)/Total

I am thinking of using eval to assign the Weight (under WeightTypeId1) to weight 1, weight 2, weight 3.... Then it is easy to do the calculations. 

|Eval werigh1= if (WeightTypeId=1, Weight, 0). But somehow I feel this is not correct...

Can someone help me with that?

Thanks a lot!

[ITWhisperer]

It is on the right lines. Try:

| eval weight1=if(WeightTypeId=1,weight,null)
| eval weight2=if(WeightTypeId=2,weight,null)
| eval weight3=if(WeightTypeId=3,weight,null)
| eval weight4=if(WeightTypeId=4,weight,null)
| eval weight5=if(WeightTypeId=5,weight,null)
| stats values(*) as * by OrderNr
| eval total=weight1+weight2+weight4+weight5
| eval yield1=weight1/total
| eval yield2=weight3/total

[zoe]

sorry @ITWhisperer , I sent the last message by mistake before finishing it. Now continues

3. in the end I woule like to see sth like this:

OrderNr  Yield1, Yield2, Total

Acutally thre are also some other varibles in each event. But what I see now is that all other vairlabes are also listed. weight 1, weight 2....... WeighTypeId...., time stamp.... hostname, source....., which I do not want to include in the result. I tried to use  |Stats Values(yield1) as yield1 by OrderNr , but it does not work. Can you help me with that?

[ITWhisperer · Accepted solution]

Since you don't want the other fields, try the alternative solution with a fields command to limit the fields

| xyseries OrderNr WeightTypeId Weight
| eval total='1'+'2'+'4'+'5'
| eval yield1='1'/total
| eval yield2='3'/total
| fields OrderNr yield1 yield2 total

[zoe]

@ITWhisperer this solution is great! the first time I have learned xyseries! Thank you a lot! I have learned a lot from you!

[zoe]

Thanks @ITWhisperer for your help!I have three questions to your solution

1. I see you used 'null' instead '0', does it mean, when the condition is not fulfilled, the variable will have no value instead of '0'? I tried '0', it is not working.....

2. You put | stats value(*) as * by OrderNr before the rest of eval commands of 'total', 'yield1' and 'Yield2'. But if I put this stats command in the end, the result does not show 'total', 'yield1' and 'yeild2'? Why does the order matter?

3. In the end I just want to see sth like this

[ITWhisperer]

1. By using null instead of zero, the field is not present for the other events, so when the events are grouped together by OrderNr with the stats command, you don't get extra zeroes that you don't need
2. The order matter because it is the stats command that is collecting all the weights into one line so the calculation can be done
3. I am not sure what you want it to look like

Did you try the alternative solution?

[zoe]

@ITWhisperer at the end of your first solution, I put | table OrderNr total yield1 yield2, then I can what I want to see. I hpe this is correct

[ITWhisperer]

Alternatively:

| xyseries OrderNr WeightTypeId Weight
| eval total='1'+'2'+'4'+'5'
| eval yield1='1'/total
| eval yield2='3'/total