Matching value from two multi-value field

[twh1]

I am working with MS-Exchange data. I am taking recipient email value and matching with user lookup for other details. Same email have multiple matching values in lookup table. I want only matching records in same row, instead of repeating it.

Ex.: I have an email [email protected] in log. I have 3 records matching in user lookup like below.

 email      first  last   id    type
[email protected]  Ram   Singh  1001   T
[email protected]  Ram   Singh  1042   C
[email protected]  Ram   Singh  1063   T

I am using below line to match recipient value and get other details from lookup.

| stats values(recipient) as recipient count by _time sender 
| mvexpand recipient
| eval recipient=lower(recipient)
| lookup users email AS recipient OUTPUT id type first last

I am getting output like below.

sender       recipient     id    type    first  last 
[email protected]  [email protected]   1001   T      Ram   Singh
                           1042   C      
                           1063   T      

But I am expecting result like this, so that i can perform some conditional action.

sender       recipient     id    type    first  last 
[email protected]  [email protected]   1001   T      Ram   Singh
[email protected]  [email protected]   1042   C      Ram   Singh
[email protected]  [email protected]   1063   T      Ram   Singh 

If I am using mvexpand command, it's providing wrong output rows.

[twh1]

Hi @to4kawa ,
I have multi value field not NULL value field. If i have only 1 multi-value field, I can use mvexpand and get the output. But I have multiple multi -value field, for which I need row with respective value.

I have made little change in output now. Hope this will bring more clarity to my question.

[to4kawa · Accepted solution]

....
| stats values(recipient) as recipient count by _time sender 
| mvexpand recipient
| eval recipient=lower(recipient)
| lookup users email AS recipient OUTPUT id
| mvexpand id
| lookup users id OUTPUT type first last

If you provide sample jpg, more clearly.