I am seeking to aggrigate a historic set of data. The problem is the data pull date, a critical peice of identification, is stored in the title of the report - and does not exist in any of the fields. There is no way to identify WHEN in time the data in a row exsisted, or if it differs from the next time that identical row appears in another report.
Example
Row ID | Categorical Variable | Categorical Date | Report Identifier |
123 | Blue | 1/1/2018 | 3/1/2018 |
456 | Red | 2/1/2018 | 3/1/2018 |
789 | Green | 3/1/2018 | 3/1/2018 |
123 | Blue | 1/1/2018 | 4/1/2018 |
456 | Red | 2/1/2018 | 4/1/2018 |
987 | Black | 3/1/2018 | 4/1/2018 |
123 | Blue | 1/1/2018 | 5/1/2018 |
987 | Black | 3/1/2018 | 5/1/2018 |
654 | Red | 4/1/2018 | 5/1/2018 |
I have tried to add a concatenated statement of the max date on a report but it returns the max date of each row... I would prefer not to load each report manually and blend/etl it since it's a standard report that should be workbenched. Is there a way to pull in data from an excel sheet's meta info or title? or a way to attach a unique constant per run? A date would be ideal but i will settle for anything i can use to compare unique id to unique id transactionally.