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.
... View more