Compare multivalues from different rows

[farhad]

In my search i have 2 rows, column specifying the week and the other column a multi-value field of EventIDs. I need to compare one row multievalue field to another row multi value field. And Output the different event values from these 2 rows.

______________________________

my search
| stats values(EventID) by week

Result:

week	values(EventID)
This week	4624 4625 4627 4634 4647 4648 4656 4658 4661 4663 4664 4670 4672 4673 4674 4688 4689 4690 4692 4693 4698
Previous Week	4624 4625 4627 4634 4647 4648 4656 4658 4661 4663 4664 4670 4672 4673 4674 4688 4689 4690 4692 4693 4698 4702 4720 4722 4724 4725

Now i desire to compare "this week" event values with "previous week" event values and table values not seen in both weeks. Any suggestions is much appreciated, thanks!

[kamlesh_vaghela · Accepted solution]

@farhad 

Can you please try this?

YOUR_SEARCH
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

My Sample Search :

| makeresults 
| eval _raw="week	EventID
This week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,100,102,103
Previous Week	4624,4625,4627,4634,4647,4648,4656,4658,4661,4663,4664,4670,4672,4673,4674,4688,4689,4690,4692,4693,4698,4702,4720,4722,4724,4725
" 
| multikv forceheader=1 
| eval EventID=split(EventID,",") 
| mvexpand EventID 
| table week EventID 
| rename comment as "Upto now is for sample data only"
| stats values(week) as weeks by EventID 
| eval status = case(mvcount(weeks)==2,"Available In Both",weeks="This week","Available In This Week",weeks="Previous Week","Available In Previous Week") 
| stats values(EventID) as EventIDs by status

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[farhad]

Work as intended, great thanks!

[ITWhisperer]

You have to use transpose to get the values into the same event so you can compare them; can then transpose again to return them to different event (if that's what you require).

| transpose 0 header_field=week column_name=events
| foreach mode=multivalue "Previous Week"
    [| eval missing=if(in(<<ITEM>>,'This week'),missing,mvappend(missing,<<ITEM>>))]
| foreach mode=multivalue "This week"
    [| eval new=if(in(<<ITEM>>,'Previous Week'),new,mvappend(new,<<ITEM>>))]
| transpose 0 header_field=events column_name=week