I'm trying to create a Union (left join) of two Excel files where one of the fields to be joined on is a calculated field.
I have some financial data within an Excel file that includes a charge code associated to a specific project. Currently I'm using a calculated field (long IF/THEN calculation) to classify each charge code with a project number. I'm then blending this file with a second Excel file that contains project description information using this project number. While this blended data has help me create a lot of useful dashboards, I'm constrained a bit by the aggregate / non-aggregate issues that occur when using blended data, so I'm trying to use a union instead. The issue I have is that when I try to do my left join (financials file left, project description right) only the organic fields appear as options to join the data on. I'd like to use the calculation I've created classifying each charge code to a project number as the field the two sheets are joined on but cannot figure out how to do so.
Any suggestions would be very helpful.
Check out the custom sql examples that utilize a Union query and see if these will work for you - Connect to a Custom SQL Query you can do a few select statements to come up with just the values desired and then output different field values for a custom field if needed.