Alteryx Designer

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

How to transform and align without creating duplicate or null values

Highlighted
11 - Bolide

This is a tough one that has had me stuck for 2 days. 

 

I have $ and Text associated with a year (Prior Year, Current Year, Next year).  I want to transpose and align the Text, $, and Year without duplicating records.  

 

Here is an example of how may data appears.  

 

ID

PROGRAM 

PROJECT 

PROJECT DESC

Prior Year Text

Current Year Text

Next Year Text

BUDGET YR

AMOUNT

16472351A

Fast Car

Materials

Funds basic research in chemistry, biology, biotechnology, and 

We did this and that in 2019

We did even more last year in 2020

We plan to do the most in 2021

Prior Year

955

16472351A

Fast Car

Materials

Funds basic research in chemistry, biology, biotechnology, and 

We did this and that in 2019

We did even more last year in 2020

We plan to do the most in 2021

Current Year

1354

16472351A

Fast Car

Materials

Funds basic research in chemistry, biology, biotechnology, and 

We did this and that in 2019

We did even more last year in 2020

We plan to do the most in 2021

Next Year

2345

16472351A

Fast Car

Structure

Funds development of primary structure

Preliminary design in 2019

Computer modeling in 2020

Will build first prototype in 2021

Prior Year

345

16472351A

Fast Car

Structure

Funds development of primary structure

Preliminary design in 2019

Computer modeling in 2020

Will build first prototype in 2021

Current Year

2346

16472351A

Fast Car

Structure

Funds development of primary structure

Preliminary design in 2019

Computer modeling in 2020

Will build first prototype in 2021

Next Year

5679

123456A

Fast Bike

Frame

Aerodynamic super light frame

Tried this in 2019

3 prototypes in 2020

Test in TOS 2021

Prior Year

54564

123456A

Fast Bike

Frame

Aerodynamic super light frame

Tried this in 2019

3 prototypes in 2020

Test in TOS 2021

Current Year

654651

123456A

Fast Bike

Frame

Aerodynamic super light frame

Tried this in 2019

3 prototypes in 2020

Test in TOS 2021

Next Year

468473

 

Here is my desired output -- unless of course, there is a more elegant solution I am not seeing.

 

 

ID

PROGRAM

PROJECT 

PERIOD

COMMENTS

AMOUNT

16472351A

Fast Car

Materials

Prior Year

We did this and that in 2019

955

16472351A

Fast Car

Materials

Current Year

We did this and that in 2020

1354

16472351A

Fast Car

Materials

Next Year

We did this and that in 2021

2345

16472351A

Fast Car

Structure

Prior Year

Preliminary design in 2019

345

16472351A

Fast Car

Structure

Current Year

Computer modeling in 2020

2346

16472351A

Fast Car

Structure

Next Year

Will build first prototype in 21

5679

12356A

Fast Bike

Frame

Prior Year

Tried this in 2019

54564

12356A

Fast Bike

Frame

Current Year

3 Prototypes in 2020

654651

12356A

Fast Bike

Frame

Next Year

Test in TOS in 2021

468473

 

I've tried various flips and flops using the transpose tool and I just can't seem to get it exactly how I want it. I keep getting duplicate amounts. Help.

 

Thanks. 

 

Highlighted
7 - Meteor

Hi @hellyars 

After transpose, simply compare the strings in budget year and transposed field and you can achieve this. See the attached workflow

 

Cheers!!

Highlighted
11 - Bolide

@mtakka1  Nice. I can use this tip.  I can almost get it to work, but some of the nuances of the real data are causing some problems. 

 

This is not really solved, but I am going to post it as a solution as it works with the sample data I provided. 

Labels