Inputting specific columns from two excel files

[Chirag_Gandhi07]

Hi so I am new to Alteryx and I need to create a model where I check whether or not the contract numbers and asset numbers from two different excel files match. The problem is that both have irrelevant columns that I don't need in my workflow. So I was wondering if it is possible to input specific columns from two excel files? At this point, I was thinking of just creating a new excel file with the columns that I require. Is that my only option? All answers would be greatly appreciated!

Thanks,
Chirag Gandhi

[danrh · Accepted solution]

The easier option is to use an Input Data tool to bring the whole sheet in, then add a Select tool to only keep the columns you're concerned about:

[Chirag_Gandhi07]

Yes that worked! Thanks. Also, I don't want to join the records but I want them to align side by side. Right now when I use the union tool, they both appear but not next to each other. They will have the columns next to each other but the contract numbers from the first excel file start from record 1 and the contract numbers from the other excel file will start from record 28,303. Is there a way to arrange them so that they both start from record 1?

[danrh]

So record 1 from both excels should be on the the same row?  Same with record 2, record 3, etc? Try the Join tool, but choose to Join by Record Position:

This will line them up side by side.

[Chirag_Gandhi07]

Right the join tool works but the problem is that it only shows the contract numbers that match. 28303 natch but there are a total of 28498 and I need to identify which don't match. When I use the join tool, it only shows the 28303 that match

[Chirag_Gandhi07]

Is there a way to make them align using the union tool?

[danrh · Accepted solution]

The Join tool has three outputs - L,J, and R.  The J stream is the records that match, so if you're looking for the non-matching you might want to look at the L and R streams.  It would be very helpful to get some sample data - any chance you can remove/change sensitive data and post some?

[Chirag_Gandhi07]

I will try to send some over a little later today. The problem is that the two contract number columns from both excel files don't line up, meaning that they match in each row., same with the asset numbers. If I were to create a conditional statement, where if they match, it would output a 1, and if they don't, it would output a 0, would the contract numbers need to match in each row?

[Krausn2 · Accepted solution]

Hello,

I wouldn't suggest doing that.  The previous answer is the easiest solution to your workflow.  I would look at the L or R output from your 'join tool' for the specific items from either worksheet that didn't join successfully.  Then if  the column layout is the same  you can 'union' both as one output for a further comprehensive review.  I would suggest if you were to do that, to use the formula tool to create a column that delineates the file name so you have have the origin, or if the input file is excel to have that included in your input.

[Chirag_Gandhi07]

Yes, that worked. Thank you both for your help! Really appreciate it