Hello,
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 desk_id & Counter_id is similar.
Also Counter_ID & Customer_name belongs to Source A where Desk_id & Purchased_item 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.
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
hi, @sharif_ahmmad
how about this?
@sharif_ahmmad your data is missing the correlation between Customer_Name and Purchased_ID for through the counter. Please add that details for the community to assist you better.
@niketnilay hi, i added more details. Kindly Let me know if it make sense now.