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

[atarahoward]

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?

[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

[atarahoward · Accepted solution]

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"

[cjaneczko]

Use a Select tool before the join on both files and change the Sales Doc No and Item both to VW_String 255. This should solve the problem. 

[binu_acs · Accepted solution]

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

[atarahoward]

I'm now able to run with that update to VW_string, thank you!

I'm now seeing that my match is pulling different SD item/Sales order Item numbers, as if it's only returning matches on sales doc/Order num. How can I get it to only return lines where both the sales order and line item number match?

[atarahoward]

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