Merging and counting 3 data sets

[chaalz]

I have 3 data sets that I'm trying to merge and count.

Data set 1

my_id   |  company_id  |  company_name  | my-type
100     |  8634535     |  Target        | COMP
200     |  0583509     |  Disney        | COMP
300     |  2095497     |  Starbucks     | COMP
400     |  6433241     |  Microsoft     | COMP

Data Set 2

some-id  |  my-group-name  | my-type
100      |  ABC            | GROUP
200      |  EFG            | GROUP
400      |  XYZ            | GROUP

Data Set 3

some-id  |  error-code |  error-descr    | my-type
100      |  900        |  descr for 900  | ERR
200      |  922        |  descr for 922  | ERR
200      |  923        |  descr for 923  | ERR

========

Results I’m trying to get:

COMPANY_ID  |  COMPANY_NAME |  GROUP  |  ERR_CODE   |  ERR_DESCR | COUNT | PERCENT
8634535     |  Target        |  ABC         |  900        |  descr for 900   | 5     | 10
0583509     |  Disney        |  EFG         |  922        |  descr for 922   | 10    | 20
0583509     |  Disney        |  EFG         |  923        |  descr for 923   | 10    | 20
2095497     |  Starbucks     |                 |                 |                          | 23    | 46
6433241     |  Microsoft     |  XYZ         |               |                          | 2     | 4

I've tried joining the data but I only seem to get rows where data is available in all data sets. My counts are off. It looks like the results of the last join are just being repeated. In the joins I specify JOIN_ID since the values are stored in different fields (field my-id in data set 1 and field some-id in data sets 2 and 3). Maybe this is the issue?

My search:

index="index1" my-type="COMP"
| rename my_id as JOIN_ID, company_id as COMPANY_ID, company_name as COMPANY_NAME

| join type=left max=10 JOIN_ID [search index="index2" my-type="GROUP"
| table my-group-name
| rename my-group-name as GROUP ]

| join type=left max=10 JOIN_ID [search index="index2" my-type="ERR"
| table error-code, error-descr
| rename error-code as ERR_CODE, error-descr as ERR_DESCR]

top COMPANY_ID  , COMPANY_NAME ,  GROUP  ,  ERR_CODE   ,  ERR_DESCR 
| rename count as COUNT, percent as PERCENT | eval PERCENT=round(PERCENT,2) | addcoltotals COUNT

I tried top and stats as well but same results. Any pointers? Thank you.

[to4kawa]

| makeresults 
| eval _raw="my_id,company_id,company_name,my_type
100,8634335,Target,COMP
200,0583509,Disney,COMP
300,2095497,Starbucks,COMP
400,6433241,Microsoft,COMP"
| multikv forceheader=1 
| table my_id,company_id,company_name,my_type
| append [ | makeresults
| eval _raw="some_id,my_group_name,my_type
100,ABC,GROUP
200,EFG,GROUP
400,XYZ,GROUP"
| multikv forceheader=1 
| table some_id,my_group_name,my_type]
| append [ | makeresults
| eval _raw="some_id,error_code,error_descr,my_type
100,900,descr for 900,ERR
200,922,descr for 922,ERR
200,923,descr for 923,ERR"
| multikv forceheader=1 
| table some_id,error_code,error_descr,my_type]
| eval some_id=coalesce(my_id,some_id)
| stats values(*) as * by some_id
| fields - my_type error_code
| mvexpand error_descr
| eval error_code=mvindex(split(error_descr," "),-1)
| table company_id company_name my_group_name error_code error_descr 

try stats