How to compare two tables and store the values in new table

[pandeyraunak007]

Hello Folks,

I have two excel sheets both contains similar values. example:

Table 1: 

account number name street city state postal code

935480	Bruen Group	5131 Nienow Viaduct Apt. 290	Port Arlie	Alabama	14118
371770	Cruickshank-Boyer	839 Lana Expressway Suite 234	South Viviana	Alabama	57838
548367	Spencer, Grady and Herman	65387 Lang Circle Apt. 516	Greenholtbury	Alaska	58394
296620	Schamberger, Hagenes and Brown	26340 Ferry Neck Apt. 612	McCulloughstad	Alaska	74052
132971	Williamson, Schumm and Hettinger	89403 Casimer Spring	Jeremieburgh	Arkansas	62785

Table 2:

CustomerID name address OrderID    

935480	Bruen Group	5131 Nienow Viaduct Apt. 290	255848
371770	Cruickshank-Boyer	839 Lana Expressway Suite 234
548367	Spencer, Grady and Herman	65387 Lang Circle Apt. 516	8876665
296620	Schamberger, Hagenes and Brown	26340 Ferry Neck Apt. 612	898797
132971	Williamson, Schumm and Hettinger	89403 Casimer Spring	8797989

I want to compare both tables and store the values as Matched: "YES" or "NO" in the new table.

[JordyWegman]

Hi,

What you can do is the following:

Table1:
Load
  *
From [YourSource1](qvd)
;

Qualify;
Unqualify 'account';

Left Join (Table1)
Table2:
  *
From [YourSource2](qvd)
;

FinalTable:
Load
  *,
  IF([Field1] = [YourFieldFromTable2],'Yes','No') as _indYesNo
Resident Table1;
// Create this for all the fields if you want or put and statements in the IF.

Drop table Table1;

Jordy

Climber

[pandeyraunak007]

Hi Jordy,

I used the solution you advised but the out is not what i expected 

Table1:
LOAD
Customer_name,
 Cust_ID as  Key,
"Country Code",
"Country Name",
"Industry Name",
"Order Date",
"Coupon Type",
"Interest Rate",

FROM source;

Qualify;
Unqualify 'Key';

Left Join(UI)
table1:

LOAD
CustomerID as Key,
COUNTRY_CD,
COUNTRY_NM,
Order_DT,
COUPON_TYPE,
FROM  source2;

FinalTable:

Load
*,

IF(Country_CD= "Country Code"",'Yes','No') as _indYesNo
Resident Table1;
Drop Table Table1;

the Final table has 11023630 number of rows.

Delete

[pandeyraunak007]

Hi Jordy,

I used the solution you advised but the out is not what i expected 

Table1:
LOAD
Customer_name,
 Cust_ID as  Key,
"Country Code",
"Country Name",
"Industry Name",
"Order Date",
"Coupon Type",
"Interest Rate",

FROM source;

Qualify;
Unqualify 'Key';

Left Join(Table1)
Table2:

LOAD
CustomerID as Key,
COUNTRY_CD,
COUNTRY_NM,
Order_DT,
COUPON_TYPE,
FROM  source2;

FinalTable:

Load
*,

IF(Country_CD= "Country Code"",'Yes','No') as _indYesNo
Resident Table1;
Drop Table Table1;

the Final table has 11023630 number of rows.

Delete

[JordyWegman]

If this is the actual script, you need to change it a bit. Try this:

Table1:
LOAD
Customer_name,
 Cust_ID as  Key,
"Country Code",
"Country Name",
"Industry Name",
"Order Date",
"Coupon Type",
"Interest Rate",

FROM source;

Qualify;
Unqualify 'Key';


Left Join(Table1)
LOAD
CustomerID as Key,
COUNTRY_CD,
COUNTRY_NM,
Order_DT,
COUPON_TYPE,
FROM  source2;

FinalTable:

Load
*,
IF( [Country_CD] = [Country Code],'Yes','No') as _indYesNo
Resident Table1;

Drop Table Table1;

But what are the row amounts of the tables stand alone? Is one also 11M? And do you have duplicates in one?

Jordy

Climber

[pandeyraunak007]

Both the tables has 16 columns and the table 1 has 2801 rows and Table to has 3923 rows,

Where each rows represents customers data and I want to check if the both table has consistent data.

if not then indicate as 'No'.

Example

Table1

Key               Name                                                                          address                                                  

935480	Bruen Group	5131 Nienow Viaduct Apt. 290
371770	Cruickshank-Boyer	839 Lana Expressway Suite 234
548367	Spencer, Grady and Herman	65387 Lang Circle Apt. 516
296620	Schamberger, Hagenes and Brown	26340 Ferry Neck Apt. 612

Table2

935480	Bruen Group	5131 Nienow Viaduct Apt. 290
371770	Cruickshank-Boyer	839 Lana Expressway Suite 234
548367	Spencer, Grady and Herman	65387 Lang Circle Apt. 516
296620	Schamberger, Hagenes and Brown	26340 Ferry Neck Apt. 612
132971	Williamson, Schumm and Hettinger	89403 Casimer Spring

and the expected output is

935480	Bruen	5131 Nienow Viaduct Apt. 290	No
371770	Cruickshank-Boyer	839 Lana Expressway Suite 234	Yes
548367	Spencer, Grady and Herman	65387 Lang Circle Apt. 516	Yes
296620	Schamberger, Hagenes and Brown	26340 Ferry Neck Apt. 612	Yes
132971	Williamson, Schumm and Hettinger	89403 Casimer Spring	Yes

[JordyWegman]

Check this script with your example data:

Table:
Load * Inline [
Key,   Name,      address                                                  
935480,	Bruen Group,					5131 Nienow Viaduct Apt. 290	 	 	 
371770,	Cruickshank-Boyer,				839 Lana Expressway Suite 234	 	 	 
548367,	Spencer Grady and Herman,		65387 Lang Circle Apt. 516	 	 	 
296620,	Schamberger Hagenes and Brown,	26340 Ferry Neck Apt. 612
];

Outer Join (Table)
Load * Inline [
Key,   Name2,      address2  
935480,	Bruen,	5131 Nienow Viaduct Apt. 290	 	 	 
371770,	Cruickshank-Boyer,	839 Lana Expressway Suite 234	 	 	 
548367,	Spencer Grady and Herman,	65387 Lang Circle Apt. 516	 	 	 
296620,	Schamberger Hagenes and Brown,	26340 Ferry Neck Apt. 612	 	 	 
132971,	Williamson Schumm and Hettinger,	89403 Casimer Spring
];


FinalTable:
Load 
	*,
    IF(Name = Name2,'Yes','No') as NameCheck
Resident Table;

This is the outcome:

That should be the result right?

Jordy

Climber