Problem With keeping relevant data in the same row while expanding multi value fields

[sharif_ahmmad]

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.

[to4kawa]

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?

[niketn]

@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.

[sharif_ahmmad]

@niketnilay hi, i added more details. Kindly Let me know if it make sense now.