Hi ,
I have two tables ,i need to compare result of each column of table A with Table B and if there is any mismatch then i need to show those results
EXAMPLE:
Table A
ID | NAME | TYPE |
1 | TESLA | VARCHAR |
2 | SWIFT | INT |
TABLE B
ID | NAME | TYPE |
1 | TESLA | INT |
2 | SWIFT | INT |
Result:
IDA | NAMEA | TYPEA | IDB | NAMEB | TYPEB |
1 | TESLA | VARCHAR | 1 | TESLA | INT |
Any suggestions please?
| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,3,VARCHAR2,HYP,BNAK
1,MAND,3,VARCHAR2,HYP,HYP100
1,MAND,3,VARCHAR2,HYP,HYP101"
| multikv forceheader=1
| eval table="A"
| append
[| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,2,VARCHAR2,HPR,BNAK
1,MAND,3,INT,HPR,HYP100
1,MAND,3,VARCHAR2,HPR,HYP101"
| multikv forceheader=1
| eval table="B"]
| table Column_id,Column_name,Data_length,Data_type,DBName,Table
| eventstats count by Column_id Column_name Data_length Data_type Table
| where count=1
search table A
| eval table="A"
| append [search table B | eval table="B"]
| dedup id name type
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id
@ITWhisperer Thank you for the response.
If i use the above query then i get result as below
Result:
IDA | NAMEA | TYPEA | IDB | NAMEB | TYPEB |
1 | TESLA | VARCHAR | 1 | TESLA | INT |
2 | SWIFT | INT |
|
|
|
Here i get result of the mismatch plus the table A Data. But i need to get only the mismatch value. Can you suggest
search table A
| eval table="A"
| append [search table B | eval table="B"]
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id
Remove the dedup - or did you only want the mismatched fields e.g. type in this instance?
@ITWhisperer YES basically i want to compare each cloumn of both the csv and if there is any mismatch between any of the values in any columns then i need to show only those mismatch vales.Can you please help
search table A
| eval table="A"
| append [search table B | eval table="B"]
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(name) as names values(type) as types values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id
| eval nameA=if(mvcount(names)!=1,nameA,null())
| eval typeA=if(mvcount(types)!=1,typeA,null())
| eval nameB=if(mvcount(names)!=1,nameB,null())
| eval typeB=if(mvcount(types)!=1,typeB,null())
| table id nameA typeA nameB typeB
@ITWhisperer Thank you for the response.Sorry for the confusion. The below is my actual data from two lookups
Lookup1
Column_id | Column_name | Data_length | Data_type | DBName | Table |
1 | MAND | 3 | VARCHAR2 | HYP | BNAK |
1 | MAND | 3 | VARCHAR2 | HYP | HYP100 |
1 | MAND | 3 | VARCHAR2 | HYP | HYP101 |
Lookup2
Column_id | Column_name | Data_length | Data_type | DBName | Table |
1 | MAND | 2 | VARCHAR2 | HPR | BNAK |
1 | MAND | 3 | INT | HPR | HPR100 |
1 | MAND | 3 | VARCHAR2 | HPR | HPR101 |
Here i want to compare table BNAK OF columnid 1 with second lookup where table is BNAK AND COLUMNID IS 1.Similary for other table and column ID as well.
So my result should be
Column_id | Column_name | Data_length | Data_type | DBName | Table |
1 | MAND | 3 | VARCHAR2 | HYP | BNAK |
1 | MAND | 2 | VARCHAR2 | HPR | BNAK |
1 | MAND | 3 | VARCHAR2 | HYP | HYP100 |
1 | MAND | 3 | INT | HPR | HPR100 |
Please suggest
| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,3,VARCHAR2,HYP,BNAK
1,MAND,3,VARCHAR2,HYP,HYP100
1,MAND,3,VARCHAR2,HYP,HYP101"
| multikv forceheader=1
| eval table="A"
| append
[| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,2,VARCHAR2,HPR,BNAK
1,MAND,3,INT,HPR,HYP100
1,MAND,3,VARCHAR2,HPR,HYP101"
| multikv forceheader=1
| eval table="B"]
| table Column_id,Column_name,Data_length,Data_type,DBName,Table
| eventstats count by Column_id Column_name Data_length Data_type Table
| where count=1
@ITWhisperer Thank You ! It worked