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
Solved! Go to Solution.
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!
@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.
Hi @alexnajm ,
Thanks for the idea . How is it different from using summarize tool and grouping on all columns .
You can do that too @Nandakishore - whatever way works to get your datasets unique and therefore you can use the Join tool!
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.
@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?
As mentioned, another thread with more detail (including expected output) would be better so as to not overlap different questions here!