We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

How to Create calculation columns dynamically for a set of columns

ravi061
8 - Asteroid

Hi,

 

I got urgent requirement to add "difference" columns dynamically for every IDs Columns, shown as below table, Calculation for the "Difference" column is  [ (P+N)-K ]

Below case is for few ID columns, but i have nearly 600 ID columns.

Dates52515-K52515-P52515-NDifference53672-K53672-P53672-NDifference66822-NDifference53969-PDifference   
10/1/20221008510        -5220205.78 -14.22       
10/8/202220017012      -18350315.6210.85-23.53       
10/15/202215013015        -5197190.4524.7818.23       
10/22/20222502452.5     -2.5278.5250.4717.69-10.34       
Total70063039.5     -30.51045.5962.3253.32-29.86       

 

Can you help me how to start. Thanks for the help...

4 REPLIES 4
Miles_Waller
8 - Asteroid

I think I figured it out. Workflow is attached using a text input mirroring your data above, minus the difference columns which I'm assuming will not be there, but you should be able to input a real data file in this format and it will work. The biggest issue I see with your requirement is that we cannot have multiple columns named "Difference" so I made the difference columns follow the convention of "ID-Difference"

 

This ended up being more complex than I thought, I just built it as I thought through the process. It may be possible to replicate this output with fewer tools more elegantly, but whatever.

 

Miles_Waller_0-1686164236902.png

 

 

 

terry10
11 - Bolide

@ravi061 Here's one easy solution.

 

Capturex.PNG

ravi061
8 - Asteroid

 

Thanks for the solutions @terry10 & @Miles_Waller

flying008
13 - Pulsar

Hi, @ravi061 

 

1- This is a very interesting case, and the key point of the problem lies in creating new field columns and maintaining the original column names and order. If you don't need to maintain the original column names and order, the steps to solve the problem will become very simple in the future. Please see the following animation (only 7 steps):

录制_2023_06_09_14_18_28_929.gif

Input         
Dates52515-K52515-P52515-N53672-K53672-P53672-N   
10/1/20221008510220205.78    
10/8/202220017012350315.6210.85   
10/15/202215013015197190.4524.78   
10/22/20222502452.5278.5250.4717.69   
Total70063039.51045.5962.3253.32   
          
          
          
Output         
RecordID52515_Difference52515_K52515_N52515_P53672_Difference53672_K53672_N53672_PDates
1-51001085-14.22220 205.7810/1/2022
2-1820012170-23.5335010.85315.6210/8/2022
3-51501513018.2319724.78190.4510/15/2022
4-2.52502.5245-10.34278.517.69250.4710/22/2022
5-30.570039.5630-29.861045.553.32962.32Total

 

2- But if it is necessary to maintain the original column names and the expected order, then more steps are needed to implement it The key is to create new columns and calculate differences with as few steps as possible.

flying008_0-1686291879353.png

Output        
Dates52515-K52515-P52515-N52515-Difference53672-K53672-P53672-N53672-Difference
10/1/20221008510-5220205.78 -14.22
10/8/202220017012-18350315.6210.85-23.53
10/15/202215013015-5197190.4524.7818.23
10/22/20222502452.5-2.5278.5250.4717.69-10.34
Total70063039.5-30.51045.5962.3253.32-29.86

 

 

Labels