Insert few blank rows and also divide the Sector Numerator by its Denominator.

Hi Everyone
I need some help in my one of the problem I am facing and not able to get the solution so far. I have the below data set

Sector	Jan_2025	Feb_2025	Mar_2025	Apr_2025
Num_Auto	0	2	5	8
Deno_Auto	0	6	14	18
Num_Aviation	14	50	60	70
Deno_Aviation	50	55	65	90
Num_Cement		05	08	99
Deno_Cement		09	18	120

My Requirements:- Below are my requirements:-

a) I want to divide num / Deno for each sector. This row should be called as Error Rate. For example Feb_2025, in Num_Auto it is 2 and Deno_Auto it is 6, then I should get 2/6 = 33.33%.

b) Also, if I can get two blank rows - one above and one below of Error row.

Note:- Point to be noted here is that, I might not get the same sector all the time. This time I have received Auto, Aviation and cement, next time I might just receive Cement along with a new sector called Power.

Sector	Jan_2025	Feb_2025	Mar_2025	Apr_2025
Num_Auto	0	2	5	8
Deno_Auto	0	6	14	18

Error Rate	0	33.33%	35.71%	44.44%

Num_Aviation	14		60	70
Deno_Aviation	50		65	90

Error Rate	28%		92.31%	77.78%

Num_Cement		05	08	99
Deno_Cement		09	18	120

Error Rate		55.56%	44.44%	82.50%

Dynamic Processing

Accepted answers

Pilsner

Hello @akumar2609

I've created a workflow to try and calculate the error rate for your values, here's how I approached the challenge:

1) Create "Group" and "Group Position" IDs so we can identify which records relate to each other and the order in which they should appear.

2) Next I pivoted the data so all numeric values were in a single column.

3) After this, I was able to split the Num and Deno records, and then join them back together, so I had their different numeric values, side by side:

4) Using a formula to calculate the error rate (then converting it to a string with the % symbol), I was then able to pivot the data back round so it looked more like a table again:

5) Now I just had to label these new records with "error rate" and create the blank rows. I used the "Group Position" to define the order in which they would all appear.

6) Unioning the original data, the error rates, and the blank rows back together, and then sorting on the group & group position, I was able to get to an output which I believe reflects your solution.

I have attached the workflow below to try and help further, please let me know how you get on.

Regards - Pilsner

New Workflow1.yxmd

All comments

jrlindem

Hey again,

I see you are expanding on your initial need (Solved: Re: Can we divide two rows data? - Alteryx Community).

You could consider making a flag field to identify each "root" (i.e. Auto vs Aviation vs Cement) and then using Transpose and Crosstab to flip the data horizontal to run the computation per "root" and the flipping it back.

The example from the previous thread should be a useful start to get you going on this too. I may have some time later to build out a workflow, but see if that "concept" gets you started.

-Jay

akumar2609

Can you share a quick workflow to show how to achieve this?

jrlindem

Thanks @Pilsner - You got back around to this one before I could. Love the illustrated and described solution (as usual 😀) -Jay

Pilsner

Thank you very much @jrlindem!

flying008

Hi, @Pilsner

Could you please tell me, where is the 'Cross Tab Clean' tool or macro as your workflow?

录制_2026_02_26_11_54_00_398.gif

Input					Output
Sector	Jan_2025	Feb_2025	Mar_2025	Apr_2025	Sector	Jan_2025	Feb_2025	Mar_2025	Apr_2025
Num_Auto	0	2	5	8	Num_Auto	0	2	5	8
Deno_Auto	0	6	14	18	Deno_Auto	0	6	14	18
Num_Aviation	14	50	60	70
Deno_Aviation	50	55	65	90	Error Rate		33.33%	35.71%	44.44%
Num_Cement		5	8	99
Deno_Cement		9	18	120	Num_Aviation	14	50	60	70
					Deno_Aviation	50	55	65	90

					Error Rate	28%	90.91%	92.31%	77.78%

					Num_Cement		5	8	99
					Deno_Cement		9	18	120

					Error Rate		55.56%	44.44%	82.50%

Pilsner

Hello @flying008,

Good spot, that's my bad. The standard cross-tab tool now has a "retain special characters" option, so the crosstab clean is no longer needed. I must have used it out of habit 😄. I've attached a new copy of the workflow below with the standard cross-tab clean. Below is a picture of the "new" option I have selected.

Screenshot 2026-02-26 091457.png

I hope this helps but please let me know if not. If you still need the cross tab clean macro, I hence packaged it in the .yxzp below (although like previously mentioned, I believe the standard cross tab tool is now sufficient)!

Regards - Pilsner

New Workflow1.yxzp

flying008

Hi, @Pilsner

Thank you for your share ! I will like it.