How do I compare these two data sets to find matches based on two columns?

I'm trying to compare the sales order and line item number between two lists in the data sets attached. I've tried both join and union and get errors. Can someone show me how to compare these two data sets for matches based on sales order/doc number and corresponding line item/sd item number (including columns for the sales and costs) and return a list of sales orders in both data sets?

Sales Data Sample.xlsx

Accrual Data Sample.xlsx

Datasets

Developer

Output

Dynamic Processing

Accepted answers

atarahoward

Thank you, the file you uploaded does not open on my PC. When I update based on your screenshot I still get the error "String fields can only be joined to other string fields"

binu_acs

@atarahoward attaching the workflow again and the input file I used for this

Accrual Data Sample.xlsx

Sales Data Sample.xlsx

Join_300523.yxmd

All comments

cjaneczko

Add a Select tool before the Join tool for both of your inputs. Update the Sales Doc No and Line Item to VW_String 255. Then you should be able to join on those two fields. By default one of the documents was setting the value of the Sales Doc No and Line Item to Double. The join wont work if the data types are not the same in both inputs.

binu_acs

@atarahoward Before joining the datasets you need to do some data cleansing

Join_300523.yxzp

atarahoward

Thank you, the file you uploaded does not open on my PC. When I update based on your screenshot I still get the error "String fields can only be joined to other string fields"

binu_acs

@atarahoward attaching the workflow again and the input file I used for this

Accrual Data Sample.xlsx

Sales Data Sample.xlsx

Join_300523.yxmd

atarahoward

I just realized I needed to add the SD item/sales order item num to the join!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703