Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to fetch the duplicate values in two tables

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..............

19 Replies
Not applicable
Author

HI Vijay,

I no need to remove those duplicates.

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

Not applicable
Author

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;


Capture.PNG

Not applicable
Author

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?


Not applicable
Author

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;

Not applicable
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)