Fill the blank in a table

[melonman]

Hi

I created a table using stats, it looks like Table A below
What I am trying to do is make create Table B.

Table A

Key1               Key2             FiledA    FiledB    FiledC   FiledD
------------------------------------------------------------------------
Key1-1             Key2-1            A-1      B-1       C-1      D-1
                                     A-2      B-2       C-2      D-1                                                   
Key1-2             Key2-2            A-3      B-2       C-3      D-2
                                     A-4      B-4       C-3      D-3 

Table B

Key1               Key2             FiledA    FiledB    FiledC   FiledD
------------------------------------------------------------------------
Key1-1             Key2-1            A-1      B-1       C-1      D-1
Key1-1             Key2-1            A-2      B-2       C-2      D-1
Key1-2             Key2-2            A-3      B-2       C-3      D-2
Key1-2             Key2-2            A-4      B-4       C-3      D-3

I was trying to do this by mvzip and mvexpand, but there are multiple multivalue fields,
so I need to do mvzip many times.

Any idea or approach to this would be appreciated.

[lguinn2 · Accepted solution]

Try this

(source = S1) OR (source = S2) 
| stats count by key1 key2 fieldA fieldB fieldC  fieldD
| fields - count

but it's also difficult to say what will work when we have no examples of the actual data.

[vccsupport]

From fieldA thru fieldD are not multivalue before connecting with STATS comamnd. This table is the results from STATS command.
Please suppose fieldA,B are defined by S1, and filedC,D are defined by S2. And these sources can connect by key1 and key2.
The search text is.....
(source = S1) OR (source = S2) | stats values(fieldA) as filedA values(fieldB) as fieldB values(fieldC) as fieldC values(fieldD) as fieldD by key1 key2 | table key1 key2 fieldA fieldB fieldC fieldD. Hope this will help.

[yannK]

hard to tell without knowing the search that produced this ( and which fields are single or multivalue)