Merging 2 datasets based on specific set of conditions

Question

Hi,

I have 2 datasets that I need to merge to get only specific sets of records. In Table 1, highlighted in green shows records with Max(Month) and Max(year1) for every Item + year.  My output should have all records from Table 1 and append records from table 2 if there is forecast information available for months after Max(Month) & Max(year1) for Item + Year in Table 1 as highlighted in yellow .

Can someone help how to go about this?

Thanks!

Sampledata.xlsx

atcodedog05 · Answer

Hi @hash_89

Here is how you can do it. You can union to join data and the use unique tool to get first occurence.

Workflow:

Hope this helps : )

merge.yxmd