Join

[Satyaki04]

I want to compare a column within my data set with a column from another file. 

The second file which will be used for making comparisons will change on monthly basis.

How do i use Join function to address an issue within my workflow where the file is variable?

[BrandonB]

Will the column name be variable as well or is it just the file itself that will change?

[Satyaki04]

The column name will remain same.

[BrandonB · Accepted solution]

Some people like to use this approach to make their file input dynamic. They read in a list of file names from the directory tool, sort by the creation time to get the latest files up top, then use a sample tool to pick the top one which is the latest, and then feed the full path into the dynamic input tool so that each time the workflow runs it will pull the latest file. Because you mentioned that the column to be joined on will stay consistent, this approach should work for you. 

 

[Marko1986]

Hope you are doing well,

Could you please help me with situation that I have.

So I have two sets of data that both have 2 fields. Number and amount.

I need to join those by their numbers and that is easy because usually they are the same and we just need to check that differences between amounts are the same.

The problem is that when they ARE NOT the same, Join tool doesn't give me results that says which records were not joined because there is no "Number" from one in another stream.

Hopefully I managed to explain what bothers me A LOT!

Thank you in advance!

[BrandonB]

Hi @Marko1986 

You most likely need a union tool after the join tool that has the L, the J, and the R all connected to it. This will give you what is referred to as a full outer join which contains the data that is joined as well as the data that is not. I have attached a picture below and a workflow sample. You can add a formula tool after the union to compare the amount values, or you could filter for where [Amount] = [Right_Amount] if you wanted to further narrow to specific criteria.