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 NamePrice Currency NamePrice Currency A100USD A100USDA100USD A100USDA200USD A200USD Expected Results NamePrice Currency NamePrice Currency A100USD A100USDA100USD A100USDA200USD A200USD Actual Results NamePrice Currency NamePrice Currency A100USD A100USDA100USD A100USDA200USD A200USDA100USD A100USDA100USD A100USD This works in excel vlookup but fails using join tool . Thanks

Dataset comparision resulting in duplicates

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

Datasets

Accepted answers

alexnajm

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

All comments

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:

Dataset comparision resulting in duplicates.yxmd

Nandakishore

Hi @alexnajm ,

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

alexnajm

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!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335