Hi All
i am struggling with a query and appreciate some help please
i received the data on csv file - timestamp is today
i'm interested in 3 fields Account_No , Total and Order_Date
My view looks like this:
Account_No Total Order_Date
123 15.00 1/01/2023
123 35.00 15/02/2023
123 45.00 19/02/2023
456 15.00 1/01/2023
456 50.00 25/01/2023
456 10.00 19/02/2023
I'd like a view like this
Account_No Jan Feb
Total Sum 123 15.00 80.00
456 65.00 10.00
My main issue is using the eval to change the date format that appears in the csv file from 01/01/2023 to read January 2023 or even just January will probably do for this exercise.
I've come up with this so far
| eval Order_Date = replace(Order_Date,"01", "January") but firstly i see 06/January/2023 and not just January
my other issue is that if any other month has 01 in it (for example 01/07/2023 it appears like this January/07/2023)
Any ideas?
Also i started looking at stats list command to group all Account_Nos together
main search.....
| fields Order_Date Account_No Total
| stats list(Total) as Total by Account_No
i am unable to figure out how to get the subtotals for each Account_No
Any pointers would be appreciated
Thank you 🙂
Hi @PaulaCom,
Here's a way to convert your date to "Jan", "Feb" etc:
strftime(date_field, format) see docs
We can use the field Order_Date like this:
eval month = strftime(strptime(Order_Date, "%d/%m/%Y"), "%b")
That adds another step of converting the date to a unix timestamp, then converting that timestamp to the Month in english.
Now that we have the month, we can make it a field by using special curly brackets:
| eval {month} = Total
That will create a field called "Jan" or "Feb" with the value of the total for sales.
Here's the Search all together:
|makeresults | eval data="Account_No=\"123\", Total=\"15.00\", Order_Date=\"1/01/2023\"@@Account_No=\"123\", Total=\"35.00\", Order_Date=\"15/02/2023\"@@Account_No=\"123\", Total=\"45.00\", Order_Date=\"19/02/2023\"@@Account_No=\"456\", Total=\"15.00\", Order_Date=\"1/01/2023\"@@Account_No=\"456\", Total=\"50.00\", Order_Date=\"25/01/2023\"@@Account_No=\"456\", Total=\"10.00\", Order_Date=\"19/02/2023\""
| makemv data delim="@@" | mvexpand data | rename data as _raw | extract
``` The above just creates the test data```
| eval month = strftime(strptime(Order_Date, "%d/%m/%Y"), "%b")
| stats sum(Total) as Total by Account_No, month
| eval {month}=Total
| fields - Total, month
| stats sum(*) as * by Account_No
| table Account_No, Ja*, Fe*,Ma*,Ap*,Ma*,Jun*, Jul*,Au*,Se*,Oc*,No*,De*
The last table bit at the end is so that the months are listed in the right order.
The result is:
Hopefully that gets you closer to what you were looking for.
Cheers,
Daniel