Hi @StrobelJones, We could do this a couple of ways in Paxata based on how the data is coming in. Would you please answer the below questions so that I could guide you towards the right solution?
September Hierarchy Dataset and October Hierarchy Dataset - are they different versions of the same dataset OR are they two different datasets? What will happen for the month of November?
How is the source data coming in? Is it an incremental load or a full load every time this lookup needs to happen?
If both sides are incremental load, we could look up the incremental load with incremental load and then combine the outputs iteration over iteration to build a master list. If one side is incremental, while the other is full load, we will have to convert the incremental side to a master list by appending with the previous master list. Once both sides have the same granularity of data (incremental or full list) a lookup could be performed based on the month and associate id.
Right now, the Hierarchy data is loaded in as different datasets, but I could change that to be v1, v2, OR I could append together so that it's a single table and add a column to identify what month it is.
The other source data is a full load every time as we are looking at other fields on the records that change over time.