Hi I have the following search:
[my search]
|dedup @timestamp
|stats sum(json_message.amount) as "total" by json_message.currencyCode
|eval devide = if(like(json_message.currencyCode,"%IDR%") , "1748000" , "100")
|eval final=total/devide
|table json_message.currencyCode final
|rename final as "Total in real amount", json_message.currencyCode as "Currency"
In the result table, I want the total amount to be divided by 1748000 for IDR and by 100 for other currencies.
Hi @xelian,
Can you please try below query:
[my search]
|dedup @timestamp
| rename json_message.currencyCode as currencyCode, json_message.amount as amount
|stats sum(amount) as "total" by currencyCode
|eval devide = if(like(currencyCode,"%IDR%") , 1748000 , 100)
|eval final=total/devide
|table currencyCode final
|rename final as "Total in real amount", currencyCode as "Currency"
Hope this helps!!
Hi @xelian,
Can you please try below query:
[my search]
|dedup @timestamp
| rename json_message.currencyCode as currencyCode, json_message.amount as amount
|stats sum(amount) as "total" by currencyCode
|eval devide = if(like(currencyCode,"%IDR%") , 1748000 , 100)
|eval final=total/devide
|table currencyCode final
|rename final as "Total in real amount", currencyCode as "Currency"
Hope this helps!!
WoW man, rename must be first. OOOOO I read half of the SPlunk doc for if/case/string/toNumber tried 100 different scnearios. Thankssssss
Yes, you caught that correctly, always consider renaming the field name if field name cannot be a variable name for your script, for being on safe side.!!!!!
@xelian ,
Your search should work. Are you not getting the result? Suggest to remove the quotes around the numbers in the condition though it should not affect your result.
This is a run anywhere example
| makeresults |eval currencies="IDR,EUR,USD,AUD"|makemv currencies delim=","| mvexpand currencies|eval total=100
|eval divider=if(currencies=="IDR",100,10)|eval final=total/divider|fields - divider
I remove quotes but in the result always 100 is devide. I think the problem is somewhere in the if, because it is a String. I do not know I am very upset. I tried 100 different scenarios and always 100.