Dataset comparision resulting in duplicates

[Nandakishore]

Hi there ,

I have this issue of result in duplicate lines when comparing similar data sets 

Grouped on Name , Price , currency
Datset A					Dataset B
Name	Price	Currency		Name	Price	Currency
A	100	USD		A	100	USD
A	100	USD		A	100	USD
A	200	USD		A	200	USD
Expected Results
Name	Price	Currency		Name	Price	Currency
A	100	USD		A	100	USD
A	100	USD		A	100	USD
A	200	USD		A	200	USD
Actual Results
Name	Price	Currency		Name	Price	Currency
A	100	USD		A	100	USD
A	100	USD		A	100	USD
A	200	USD		A	200	USD
A	100	USD		A	100	USD
A	100	USD		A	100	USD

This works in excel vlookup but fails using join tool . 

Thanks

[alexnajm]

If you would like to replicate closer to a VLOOKUP, you can use a Find Replace tool - however remember like a VLOOKUP, it will only find one match (i.e. if there are other rows that would match, they won't come through) AND you don't have the flexibility to select multiple fields. But the Join tool is acting as expected since you have duplicate rows on Name, Price, and Currency!

[Raj]

@Nandakishore Usually what happens with Join tool is that it joins the record with all the Possible Connections
for example if your data set is added with one more entry of 100 on both side you will end up with 10 records in Output.

To tackle this you can use the FInd and repalce tool or if they are on same position in Both the Tables, add record Id to both and join on basis of record ID.

[alexnajm]

Here's a workflow illustrating the options mentioned: 

[Nandakishore]

Hi @alexnajm ,

Thanks for the idea . How is it different from using summarize tool and grouping on all columns .

[alexnajm · Accepted solution]

You can do that too @Nandakishore - whatever way works to get your datasets unique and therefore you can use the Join tool!

[Nandakishore]

Hi @alexnajm ,

Just have another question to the same workflow to use multi row formula for the below data srt : 

Call	Status	OA
A	Yes	Yes
B	Yes	Yes
C	Yes	Yes
Call	Status	OA
A	Yes	No
B	Yes	No
C	No	No

If call status is yes in all rows then OA is also yes .but if any of the rows is No for status how to stamp OA as No for all rows.

[alexnajm]

@Nandakishore I would start a new thread since this is a separate question - it would also help to have the expected output and a bit more detail on the analysis. For example, is it per unique call value?

[Nandakishore]

Hi @alexnajm ,

No there is no grouping on call . It is for all the rows. Thanks.

[alexnajm]

As mentioned, another thread with more detail (including expected output) would be better so as to not overlap different questions here!