Camparing fields from two different tables

[berryandcherry6]

Hi,

I have two Tables

Table1:

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode

From AddressTable;

Table2:

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode

From ContactTable;

Here i need to compare Street1, Street2, cellPhoneNumber, HomePhoneNumber, Pincode field values in both table, if both field values are same then i need to add and update a flag column in ContactTable,  indicating 0 for 'equal' or  1 for 'not equal'  in each row. This i need to do in Script.

[shraddha_g]

Try below

Table1:

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode

From AddressTable;

Noconcatenate

Qualify *;

Table2:

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode,

'0' as Flag

From ContactTable

Where Exists(Street1,Street1) and Exists(Street2,Street2) and Exists(cellPhoneNumber,cellPhoneNumber) and

Exists (HomePhoneNumber,HomePhoneNumber) and Exists (Pincode,Pincode);

Concatenate (Table2)

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode,

'1' as Flag

From ContactTable

Where not Exists(Street1,Street1) and Not Exists(Street2,Street2) and Not Exists(cellPhoneNumber,cellPhoneNumber) and

Not Exists (HomePhoneNumber,HomePhoneNumber) and NotExists (Pincode,Pincode);

Unqualify *;

[berryandcherry6]

HI Shraddha,

AddressTable:

LOAD * INLINE [

Street1,Street2,cellPhoneNumber,HomePhoneNumber,Pincode

delhi,,,,

kolkata,maggadi,2342342342,2342342342,456345

,korea,45645,,

,,,,

];

NoConcatenate

ContactTable:

LOAD * INLINE [

Street1,Street2,cellPhoneNumber,HomePhoneNumber,Pincode

delhi,,,,

koata,maggadi,666666,11111,9999

,korea,,,

,,,,

];

NoConcatenate

Qualify *;

Table3:

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode,

'0' as Flag

Resident ContactTable

Where Exists(Street1,Street1) and Exists(Street2,Street2) and Exists(cellPhoneNumber,cellPhoneNumber) and

Exists (HomePhoneNumber,HomePhoneNumber) and Exists (Pincode,Pincode);

Concatenate (Table3)

Load

Street1,

Street2,

cellPhoneNumber,

HomePhoneNumber,

Pincode,

'1' as Flag

Resident ContactTable

Where not Exists(Street1,Street1) and Not Exists(Street2,Street2) and Not Exists(cellPhoneNumber,cellPhoneNumber) and

Not Exists (HomePhoneNumber,HomePhoneNumber) and Not Exists (Pincode,Pincode);

Unqualify *;

drop Table ContactTable;

This is not giving proper result,

i should get flag = 1, only for 2nd and 3rd rows of table as they are changed.

[shraddha_g · Accepted solution]

PFA.