Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Dynamic Column Calculation

Verakso
11 - Bolide

Hi

 

I was wondering if there was a clever way to accomplish this:

 

I have some data like this:2019-06-20 22_46_29-Alteryx Designer x64 - Question.yxmd.png

 

The idea is to ad some sums for some of the columns, like above

 

2019-06-20 22_49_39-Alteryx Designer x64 - Question.yxmd_.png

Here I have just used the Formula tool, to accomplish this

2019-06-20 22_53_42-Alteryx Designer x64 - Question.yxmd_.png

 

All though this works, I can't quite shake the feeling that this could be done i clever way, am I right?

 

//Verakso

 

 

6 REPLIES 6
cmcclellan
13 - Pulsar

I'm interested if there's a better way, but that's the way I would do it :) 

GiuseppeC
Alteryx
Alteryx

Hi @Verakso,

 

see attached. Not necessarily a 'better' way, but it's another way! 

This is helpful when you have a long list of fields you need to sum up by row and don't want to manually add the fields one by one. Also, it can make your logic dynamic in some circumstances, for example when you want a new field in a new data set that you input to be added automatically without changing formula in your workflow. This can be achieved by selecting the *Unknown field within the Transpose/Crosstab tools.

 

Hope this helps!

 

Giuseppe

CharlieS
17 - Castor
17 - Castor

This is possible via XML editing a batch macro. Check out the attached solution to see how it could work.I just used groups of three columns, but adjusting that logic should be simple enough. 

 

20190620-DynamicFormulas.png

 

 

 

 

 

 

 

 

 

 

 

 

@Claje showed off the power of nested macro and XML editing in his impressive Inspire session. Maybe he'll share the presentation to keep that inspiration going. 

jrgo
14 - Magnetar

@Verakso ,

 

Here's another option, however, not sure how applicable this will be to whatever you need to apply to since your example suggests to sum 3 adjacent fields except for column D, but no logic provided to why that column was excluded so that's the only part of this that is not dynamic.

image.png

 

Hope this helps!

Jimmy
Teknion Data Solutions

danilang
19 - Altair
19 - Altair

Hi @Verakso et al.

 

Here's yet another way.

 

WF.png

 

The Sum Groupings holds a list of the groups of sums that you want to produce in 2 columns SumGroup and Column

 

Groups.png

 

This is joined to the transposed data table and the results are summed grouped by SumGroup and RecordID and cross tabbed by RecordID.  The dynamic rename gives you sensible column names that are joined back to the original data giving

 

Results.png

 

Change the Sum Groupings table and change the result.  The columns can even appear in multiple groups. Below I added a 4th group with all the columns (excl D) to give a grand total

 

Totals.png

 

 

Dan

 

Verakso
11 - Bolide

First of all a big shout out to @GiuseppeC@CharlieS@jrgo and @danilang !

 

Since I could not prefer one solution over another, I have marked them all!

 

It really shows the diversity of how you can do things, and also the strength of this community.

 

I love you all, and thanks for the input

 

//Verakso

Labels