HI, all

[ravindraa]

we have two systems in those systems there might be all values common values but some case not same.

Databse1: It maintain ContracID and MembershipID

Database2: it is also maintain ContracID and MembershipID  as well as some fields.

but our requierment we need to Display The common record and as well as uncommon records.how can it is possible.

this is very urgent could you give any suggesiion.

[kaushiknsolanki]

Hi,

Try this.

     Connect to Database1.

     DATA:

     Load ContracID ,MembershipID from xyztable;

     Connect to Database2.

     Concatenate (DATA)

     Load ContracID ,MembershipID from pqrtable;

     FINAL_DATA:

     load distinct ContracID, MembershipID as MEMBERSHIP_ID resident DATA;

     Drop table DATA;

     Here final table will have data from both the tables.

Regards,

Kaushik Solanki

[anbu1984]

Common records

Table1:

Sql Select ContracID & MembershipID as key From Table

Table2:

Sql Select ContracID, MembershipID From Table

Where Exists(ContracID & MembershipID,key);

Use Where Not Exists(ContracID & MembershipID,key) to get non matching records

[ravindraa]

Dear

Kaushik Solanki,

Thanks for given value answer using this we can get the all data (common and uncommon records)

here we should show the how many common records is existed

and how many uncommon records existed.

I mean common records count:

           uncommon records count:

please give me any suggestion.

[kaushiknsolanki]

Hi,

     You can just get the count and know which whether the data is common or not. If count is more then 1 then its common and if its not then its uncommon.

     Something like this.

   Connect to Database1.

     DATA:

     Load ContracID ,MembershipID from xyztable;

     Connect to Database2.

     Concatenate (DATA)

     Load ContracID ,MembershipID from pqrtable;

    FINAL_DATA:

     load ContracID, MembershipID as MEMBERSHIP_ID,count(ContactID) as COUNT resident DATA group by

     ContracID, MembershipID;

     Drop table DATA;

Regards,

Kaushik Solanki

[anbu1984]

Use Where Exists to get common records and Where Not exist to uncommon records. check my previous reply below

[its_anandrjs]

You can add a Flag field like below script as the Kaushik suggest but you already get the Count of the records

   DATA:

     Load ContracID ,MembershipID from xyztable;

    Concatenate (DATA)

   Load ContracID ,MembershipID from pqrtable;

    FINAL_DATA_Temp:

    Load ContracID, MembershipID as MEMBERSHIP_ID,count(ContactID) as COUNT

    Resident DATA group by  ContracID, MembershipID;

   Drop table DATA;

==========

  Final:

    Load ContracID, MembershipID as MEMBERSHIP_ID, COUNT,

    IF(COUNT  >= 2,'Common Record','UnCommon Record') as Rec_Details

    Resident FINAL_DATA_Temp;

    Drop FINAL_DATA_Temp;

[ravindraa]

Dear Anbu,

   Thanks for given suggestion i have checked that code it is not matching with my requirement could you give if any other suggestion.

after get the both data how can i divided common data count

                                                           uncommon data count .

[ravindraa]

Dear

Kaushik Solanki,

   After you sent above logic i am finding which is common data and uncommon records,

  we need to show per ex: database1 have 10,000 records.

                                        database 2 have 10,200 records

  in this case we need to show 10,000 as a common records

                                                  200 as a uncommon records i mean if there is any unmatched to each other.

If you have any suggestion please share.

[#10]

Table1:

LOAD * INLINE [

ContracID ,MembershipID

1,1

1,2

1,3

2,1

2,3

3,2

3,3

];

Concatenate(Table1)

LOAD * INLINE [

ContracID ,MembershipID

1,1

1,3

2,1

2,4

2,3

3,3

4,5

4,3

];

Table2:

LOAD

ContracID ,MembershipID,if(Sum(1)=2,'Y','N') AS Flag

Resident Table1

Group By ContracID ,MembershipID

;

DROP TABLE Table1;

Then on chart, you can group by Flag, sum(1) to get matched and unmatched number.