community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Dynamic Generation of Column names and its associated Values

Hi,

I have the following data set:

 

Key    Year    Value

K1       2015     100

K1       2016     200

K1       2017     300 ...

 

I want to generate the following output:

 

Key   Year Selection    Value

K1      2015-2016          -100

K1      2015-2017          -200

K1      2016-2017          -100

 

 

The idea is to generate all possible combinations of years (lower - higher) with left side being lower value and right side being higher and then subtract right from left value. These values need to be dynamic based on data. Also all possible combinations (lower to higher) needs to be there starting with smallest year to largest year.

 

Using tools like Append quickly creates huge data, so the idea is to have as efficient solution as possible... 

 

Thanks for your help!!!

 

Alteryx Certified Partner
Alteryx Certified Partner

I'm not sure there is a more efficient way to generate all combinations than a self join (Which is basically appending).

 

2018-08-16_08-03-10.png

 

My solution is attached.

 

Ben

Alteryx Certified Partner
Alteryx Certified Partner

This second attempt may be more efficient but it's hard to tell on the small sample!

 

2018-08-16_08-07-59.png

 

Ben

Because the Join tool and the Append Fields (cross join) Tool do not allow for non-equijoin (where the join condition is not equal to) there is not a way to do this natively in Atleryx.  You must first join, then filter where "year_1" < "year_2".  If the amount of data being processes is an issue, you can strip all extraneous columns off of the data, create a list of year combinations that meet your criteria, then join back any attributes needed after the combinations are created.

Labels