i have been trying to expand multi value fields from different source-type. Problem is that when i do expand link between relevant data gets lost. I have described a similar scenario below. Any help would be greatly appreciated.
Say i have below table for example. where field value of deskid & Counterid is similar.
Also CounterID & Customername belongs to Source A where Deskid & Purchaseditem belong to Source B. Customer_ID field present in both source A & B.
Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item 121 1 Jesse 1 Pen 2 Jesse 1 Pencil 3 Jesse 2 Fountain Pen 3 Paper
I want my output table to look like this
Expected Output Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item 121 1 Jesse 1 Pen 121 1 Jesse 1 Pencil 121 2 Jesse 2 Fountain Pen 121 3 Jesse 3 Paper
but unfortunately my current output looks like this
Current Output Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item 121 1 Jesse 1 Pen 121 2 Jesse 2 Pencil 121 3 Jesse 3 Fountain Pen
My Code is below for your references
Source A OR Source B | fields Customer_ID,Counter_ID,Desk_ID,Customer_Name,Purchased_Item | stats list( Counter_ID) as Counter_ID, list(Customer_Name) as Customer Name,list(Desk_ID) as Desk_ID,stats list(Purchased_Item) as Purchased_Item by Customer_ID | table Customer_ID,Counter_ID,Customer_Name,Desk_ID,Purchased_Item, | eval total= mvzip(Counter_ID,Customer_Name) | eval total= mvzip(total,Desk_ID) |eval total= mvzip(total,Purchased_Item) | mvexpand total| makemv total delim="," | eval Counter_ID=mvindex(total,0)| eval Customer_Name=mvindex(total,1)| eval Desk_ID=mvindex(total,2)| eval Purchased_Item=mvindex(total,-1)
Any tips or help would be greatly appreciated.
@sharifahmmad your data is missing the correlation between CustomerName and Purchased_ID for through the counter. Please add that details for the community to assist you better.
Source A OR Source B | stats values(Customer_Name) as Customer_Name list(Counter_ID) as Counter_ID list(Desk_ID) as Desk_ID list(Purchased_Item) as Purchased_Item by Customer_ID | eval _counter = mvrange(0,mvcount(Purchased_Item)) | stats list(*) as * by _counter | foreach * [ eval <<FIELD>> = case(mvcount(<<FIELD>>)=1,<<FIELD>>, true(), mvindex(<<FIELD>>,_counter)) ] | fields Customer_ID,Counter_ID,Desk_ID,Customer_Name,Purchased_Item
how about this?