Join Tool for Lookup tables, best practice to find new data not in a current lookup

[jaypee1217]

Hi, 

I have created a workflow that uses a lookup table to assign expense accounts to cash transaction types, thus creating a journal entry. What's the best practice for identifying new transaction types that aren't in the current look up table? Right now if a new transaction type comes through the resulting output JE file is out of balance and you have to manually dig through the data to see what cash transaction type didn't have an expense assigned to it in the look up table. 

[DataNath · Accepted solution]

Hey @jaypee1217, when conducting the join, Alteryx will output entries that go in the left input but don’t find a join from the L output anchor. The same is true for the R output anchor for those that go into the right side of the join input but don’t find a match. Results coming out of the middle J anchor are those that find an equivalent in the other table so you can therefore check the L/R outputs, depending on which way round your inputs are, to find those that don’t hit a match.

[jaypee1217]

This makes complete sense. Then I can just put an output file and the user running the workflow should always check the file and ensure it's empty. If its not then that's a new transaction type that needs to be added to the lookup tables. 

Thank you! 

[PanPP]

Hi @jaypee1217,

The join tool would be the best option as DataNath has mentioned.

If you ever need to bring these records back into the dataset, you can join them back together via a union tool as well.

Hope this helps.