ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Join and duplicate

Kk_larsen
8 - Asteroid

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)

Kk_larsen_0-1614091773723.png 

 

Dataset 2

Kk_larsen_1-1614091791585.png

 

Expected output

Kk_larsen_2-1614091813562.png

 

 

Workflow

Kk_larsen_3-1614091977426.png

 

 

 

Is that possible?

 

mot
10 - Fireball

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:

Screen Shot 2021-02-23 at 10.08.57 AM.png

 

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

Kk_larsen
8 - Asteroid

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
8 - Asteroid

Kk_larsen_0-1614095750815.png

 

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
10 - Fireball

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. 

Screen Shot 2021-02-23 at 11.31.16 AM.png

I hope this resolves your issue. Best!

Kk_larsen
8 - Asteroid

yes! Thanks very much!! Exactly like that

Labels