Join and duplicate

Kk_larsen

Hi! 

This is a fairly complicated question - but I do think that there exist a simple solution to the problem. 

I have a dataset consisting of current active project numbers and years and another dataset conssiting of all project numbers, years and what phase the project is in at that moment in time. 

I need to join these two datasets such that for each active project number the right phase is added (so join on years and project numbers). However there are instances when a project number has a phase in 2020 = phase 1 and then first in 2023 a phase 2, so when I am joining some of the projects are not joines (as these have the years 2021 or 2022 or 2024), but I would like to have those project numbers as well but then with the phase "phase 1) for those before 2023 and "phase 2" for those after 2023.

Attached is the data and the workflow 🙂

I really hope you can help!

Dataset no 1 (with the active project numners)

 

Dataset 2

Expected output

Workflow

Is that possible?

join ex.xlsx

join ex.yxmd

mot

Hi @Kk_larsen,

I have a better understanding of your issue now. In this case, you need to create a new table and tabulate the phases for each year. I did that by using Append Fields tool and populated the new table using Multi-Row tool. 

I hope this resolves your issue. Best!

join ex.yxmd