Merge (many to many) Matching in Alteryx

Question

I am trying to merge 2 Excel files with few same columns based on a primary key(duplicates).Ex: I ran this merge command in SAS and it worked perfectly.

data output_file;
          merge file_1 file_2;
          by id;
        run;

SAS Output: It has created an output file with all the columns from both the files and merged column if both column contain same column name.

DultonM · Accepted Answer

Hi @Amit786898!

It has been awhile since I've used SAS, but I believe the equivalent of your SAS query in Alteryx would be a Join tool followed by a Union tool.

In the Join tool you would pick "id" as your join field. Records where both files have the same ID will be merged and displayed in the J output of the Join tool. Any records in either file that have an id that is not present in the other file will fall out of the L or R outputs of the Join tool. You can combine the records of all 3 outputs of the Join tool using the Union tool.

In the join tool you can select/deselect what columns you want to appear in the J output. The default is to keep them all. The Union tool will automatically keep all the columns from the 3 sources, even when they are different using the default configuration.

I have attached a simple example. Let me know if I misunderstood your inquiry or if you have further questions.