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...

How to Create calculation columns dynamically for a set of columns

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.

Dates	52515-K	52515-P	52515-N	Difference	53672-K	53672-P	53672-N	Difference	66822-N	Difference	53969-P	Difference
10/1/2022	100	85	10	-5	220	205.78		-14.22
10/8/2022	200	170	12	-18	350	315.62	10.85	-23.53
10/15/2022	150	130	15	-5	197	190.45	24.78	18.23
10/22/2022	250	245	2.5	-2.5	278.5	250.47	17.69	-10.34
Total	700	630	39.5	-30.5	1045.5	962.32	53.32	-29.86

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

Developer

Input

Output

Macros

Common Use Cases

Batch Macro

Dynamic Processing

Join

Data Investigation

Accepted answers

terry10

@ravi061 Here's one easy solution.

dynamic calcs for ravi061.yxmd

All comments

Miles_Waller

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.

Create Calc Columns Dynamically.yxmd

terry10

@ravi061 Here's one easy solution.

dynamic calcs for ravi061.yxmd

ravi061

Thanks for the solutions @terry10Miles_Waller

flying008

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
Dates	52515-K	52515-P	52515-N	53672-K	53672-P	53672-N
10/1/2022	100	85	10	220	205.78
10/8/2022	200	170	12	350	315.62	10.85
10/15/2022	150	130	15	197	190.45	24.78
10/22/2022	250	245	2.5	278.5	250.47	17.69
Total	700	630	39.5	1045.5	962.32	53.32



Output
RecordID	52515_Difference	52515_K	52515_N	52515_P	53672_Difference	53672_K	53672_N	53672_P	Dates
1	-5	100	10	85	-14.22	220		205.78	10/1/2022
2	-18	200	12	170	-23.53	350	10.85	315.62	10/8/2022
3	-5	150	15	130	18.23	197	24.78	190.45	10/15/2022
4	-2.5	250	2.5	245	-10.34	278.5	17.69	250.47	10/22/2022
5	-30.5	700	39.5	630	-29.86	1045.5	53.32	962.32	Total

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.

Output
Dates	52515-K	52515-P	52515-N	52515-Difference	53672-K	53672-P	53672-N	53672-Difference
10/1/2022	100	85	10	-5	220	205.78		-14.22
10/8/2022	200	170	12	-18	350	315.62	10.85	-23.53
10/15/2022	150	130	15	-5	197	190.45	24.78	18.23
10/22/2022	250	245	2.5	-2.5	278.5	250.47	17.69	-10.34
Total	700	630	39.5	-30.5	1045.5	962.32	53.32	-29.86

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703