How to Create calculation columns dynamically for a set of columns

[ravi061]

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

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

[terry10 · Accepted solution]

@ravi061 Here's one easy solution.

[ravi061]

Thanks for the solutions @terry10 & @Miles_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):

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