Hello Community,
I need to fill null value of multi-field values with any value , i.e 0 or Not found.
Here's the sample data in table
Sample Table
Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item
121 1 Pen
121 1 Pencil
Expected Output
Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item
121 0 0 1 Pen
121 0 0 1 Pencil
current Output
Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item
121 0 0 1 Pen
1 Pencil
Where what i wrote so far.
| stats list(Customer_Id) as Customer_id, stats list(Counter _Id) as Counter _id, stats list(Customer_Name) as Customer_Name,
stats list(Desk_ID) as Desk_ID, stats list(Purchased_Item) as Purchased_Item By Customer_Id | fillnull value=0 Counter_ID Customer_Name
Not sure how to proceed from here. Tried using foreach. But can't get what i want.
Any help would be really appreciated.
... | stats ...
| eval null_fill = mvrange(0,mvcount(Customer_Id))
| rex field=null_fill mode=sed "s/\d+/NA/g"
| eval Counter_ID=mvappend(Counter_ID,null_fill)
| eval Customer_Name = mvappend(Customer_Name,null_fill)
| eval Counter_ID=mvindex(Counter_ID,0,mvcount(Customer_Id))
| eval Customer_Name =mvindex(Customer_Name,0,mvcount(Customer_Id))
| table Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item
maybe works.