how to fetch the duplicate values in two tables

[#1]

hi All,

I have two tables in that tables having some duplicate values...how to get those duplicates in a separate list...

Example:

File1:                                                    file2:

barcode         values                              barcode                        values

1                       234                                 1                                   -

2                       567                                  3                                   -

3                        675                                  5                                  465

4                       452                                    6                                 -

5                        -                                       7                                  -

7                         -

Excepted output:

barcode        values

1                  

3

5

Hi all,

inner join working properly...its getting common values of the both tables it shows but don't need  all the duplicates......I need

in first file the bar code having some value but the same barcode in second file don't have any value so   I need only those duplicate values.

so identify that barcodes separately...

regards thanks

Ravi....

please help me its urgent for me..............

[#2]

HI Vijay,

I no need to remove those duplicates.

I need those duplicates in separate file....in separate table.....

[#3]

Example:

File1:                                                    file2:

barcode         values                              barcode                        values

1                       234                                 1                                   -

2                       567                                  3                                   -

3                        675                                  5                                  465

4                       452                                    6                                 -

5                        -                                       7                                  -

7                         -

Excepted output:

barcode        values

1                  

3

5

[sunny_talwar]

May be this?

Data: 

LOAD BarCode,

  If(Len(Trim(Values)) > 0, Values) as Values

INLINE [ 

    BarCode, Values 

    1, 234 

    2, 567 

    3, 675 

    4, 452 

    5 

]; 

Inner Join 

LOAD BarCode,

  If(Len(Trim(Values)) > 0, Values) as Values1

INLINE [ 

    BarCode, Values

    1 

    3 

    5, 465 

    6, 

    7 

];

FinalTable:

NoConcatenate

LOAD BarCode,

  Alt(Values, Values1) as Values

Resident Data;

DROP Table Data;

[#5]

Hi sunny ,

thank you for replay.....

by using this script I am getting output like this

bar code       values

1                      234

3                       675

5                       465

7                      -

I need in output only

1

3

5

[sunny_talwar]

Did you use the exact same script as mentioned above?

[#7]

hi sunny,

script is correct.

in first file u miss the 7 value. when ia ma adding 7 value its getting in output.

but I want remove 7 in output

see this example:

Example:

File1:                                                    file2:

barcode         values                              barcode                        values

1                       234                                 1                                   -

2                       567                                  3                                   -

3                        675                                  5                                  465

4                       452                                    6                                 -

5                        -                                       7                                  -

7                         -

Excepted output:

barcode        values

1                  

3

5

[Kushal_Chawda]

try this:

File1:

LOAD Barcode

           Value

FROM File1;

inner join(File1)

LOAD Barcode

          Value as Value1

FROM File1;

Final:

LOAD *

WHERE len(trim(Value))>0;

LOAD Barcode,

           alt(Value,Value1) as Value

resident File1

drop table File1;

[sunny_talwar]

Or this:

Data: 

LOAD BarCode,

  If(Len(Trim(Values)) > 0, Values) as Values

INLINE [ 

    BarCode, Values 

    1, 234 

    2, 567 

    3, 675 

    4, 452 

    5, 

    7,

]; 

Inner Join 

LOAD BarCode,

  If(Len(Trim(Values)) > 0, Values) as Values1

INLINE [ 

    BarCode, Values

    1, 

    3, 

    5, 465 

    6, 

    7, 

];

FinalTable:

NoConcatenate

LOAD BarCode,

  Alt(Values, Values1) as Values

Resident Data

Where Alt(Values, Values1) > 0;

DROP Table Data;

[#10]

Thank you sunny....

[Anil_Babu_Samineni]

Raviteja, Did you get the exact o/p which Sunny shared with you. He mentioned some code in previous but now he is updated only this Where Alt(Values, Values1) > 0; There is no difference for O/P, Correct?