Cross Tab Add percentage Column

[BRRLL99]

Hi

I have following dataset, I'm looking to percentage column to each column

in my original dataset

The no of rows in my data column are more than 1 million

and in name column more than 30+

data	Name	Value	Total	Percentage
A	AA	10	60	16.67%
A	SS	20	60	33.33%
A	RR	30	60	50%
B	AA	20	90	22.22%
B	SS	20	90	22.22%
B	RR	50	90	55.56%
C	AA	10	60	16.67%
C	SS	40	60	66.67%
C	RR	10	60	16.67%

Expected Output:

data	AA	AA - %	RR	RR - %	SS	SS - %	TOTAL
A	10	16.67%	20	33.33%	30	50.00%	60
B	20	22.22%	20	22.22%	50	55.56%	90
C	10	16.67%	40	66.67%	10	16.67%	60

[ShankerV]

Hi @BRRLL99 

One way of doing this.

Many thanks

Shanker V

[BRRLL99]

Hi

I got my expected out-put 

1. is there anyway I can reduce no of usage of tools
2. Instead of Right_AA or Right_BB , column headers should be AA - %

[ShankerV]

Hi @BRRLL99 

Removed few tools to get the same output.

Many thanks

Shanker V

[BRRLL99]

please share the workflow

[Yoshiro_Fujimori]

@BRRLL99 ,

Not sure if this works for 1MM rows, please let me know if it works.

[ShankerV · Accepted solution]

Hi @BRRLL99 

Step 1: Input

Step 2: 

Step 3: 

Step 4:

Step 5:

Step 6:

Last step :

Many thanks

Shanker V

[BRRLL99]

@Yoshiro_Fujimori @ShankerV 

thq for Quick reply

I have column headers which are scrambled

I need to arrange accordingly in order

sample data: (column Headers)

AA | CC | ZZ | BB | AA% | CC%| ZZ%|BB%

Expected output

AA| AA% | BB | BB% | CC | CC% | ZZ | ZZ%

[Yoshiro_Fujimori · Accepted solution]

@BRRLL99 ,

The column order is automatically sorted on my original workflow.

That said, if it is a separate issue, here is one solution.

Input

Workflow

Output

BTW, if you don't use "%" in the field name, you would not need to use Dynamic Rename tool, which would save your time.

[ShankerV]

Hi @BRRLL99 

I recommend to use the sort field name macro which is already available.

http://www.chaosreignswithin.com/2014/09/blog-macro-pack-2014-q3-release.html

Many thanks

Shanker V