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?

[mot]

Hi @Kk_larsen,

I am not sure I understood the problem 100%, but it seems to me that you can join by project number only and then filter by the years. Here is my solution:

The updated workflow is attached. I hope this helps. Best!

[Kk_larsen]

No not quite...

It is correct the output, but it should be done idenpendently of when the phase is or what phase it is, as it is applied on a huge dataset with many phases in different years. And then I am not sure it works that logic.

But BIG thanks for trying!! 🙂

[Kk_larsen]

everything on the left project numbers between here should be filled with phase from the previous until a new phase is met. 

I would think loop or something but I am not sure how to do this in alteryx

[mot · Accepted solution]

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!

[Kk_larsen]

yes! Thanks very much!! Exactly like that