Table Transformation

[ThankForYourHelp]

Hi there,

Thank you for your help.

I need my table 1 transform to table 2. Please be more dynamic for the tool that you will use because I will have more name in the Name column

Table 1: 

Name	Company Number	Net De	Cost De	ending
Test 1	201	17199	8599.5	8599.5
Test 1	202	839938.12	0	839938.12
Test 1	203	0	0	0
Test 1	204	1118000	25434.5	1092565.5
Test 1	205	2995105196	306998282.5	2688106913
Test 1	206	0	0	0
Test 1	207	12084.43	293.05	11791.38
Test 1	208	51312945.87	1282823.65	50030122.22
Test 1	209	226388.04	61124.77	165263.27
Test 1	210	0	0	0
Test 1	211	-172000	-32680	-139320
Test 1	212	0	0	0
Test 1	213	3499716.59	87492.91	3412223.68
Test 1	214	7938035552	198450888.8	7739584663
Test 1	215	505854497	12646362.43	493208134.6
Test 1	216	0	0	0
Test 1	217	0	0	0
Test 1	218	0	0	0
Test 1	219	0	0	0
Test 1	220	0	0	0
Test 2	201	1000	68	932
Test 2	202	48833.61	0	48833.61
Test 2	203	0	0	0
Test 2	204	65000	1478.75	63521.25
Test 2	205	174134023	17848737.36	156285285.6
Test 2	206	0	0	0
Test 2	207	702.58	17.04	685.54
Test 2	208	2983310.8	74582.77	2908728.03
Test 2	209	13162.09	3553.76	9608.33
Test 2	210	0	0	0
Test 2	211	-10000	-1900	-8100
Test 2	212	0	0	0
Test 2	213	203471.9	5086.8	198385.1
Test 2	214	461513694.9	11537842.37	449975852.5
Test 2	215	29410145.18	735253.63	28674891.55
Test 2	216	0	0	0
Test 2	217	0	0	0
Test 2	218	0	0	0
Test 2	219	0	0	0
Test 2	220	0	0	0
Test 3	201	45	322	-277
Test 3	202	322	0	322
Test 3	203	0	434	-434
Test 3	204	1234	2	1232
Test 3	205	252525	34343	218182
Test 3	206	0	0	0
Test 3	207	702.58	17.04	685.54
Test 3	208	234234	525	233709
Test 3	209	55	3553.76	-3498.76
Test 3	210	0	0	0
Test 3	211	-10000	-1900	-8100
Test 3	212	0	0	0
Test 3	213	203471.9	5086.8	198385.1
Test 3	214	6666	233	6433
Test 3	215	22222	55	22167
Test 3	216	0	0	0
Test 3	217	0	0	0
Test 3	218	0	0	0
Test 3	219	0	0	0
Test 3	220	0	0	0

Table 2:

Company Number	Net De	Net De	Net De	Cost De	Cost De	Cost De	Ending	Ending	Ending
	Test 1	Test 2	Test 3	Test 1	Test 2	Test 3	Test 1	Test 2	Test 3
201	17199	1000	45	8599.5	68	322	8599.5	932	-277
202	839938.12	48833.61	322	0	0	0	839938.12	48833.61	322
203	0	0	0	0	0	434	0	0	-434
204	1118000	65000	1234	25434.5	1478.75	2	1092565.5	63521.25	1232
205	2995105196	174134023	252525	306998282.5	17848737.36	34343	2688106913	156285285.6	218182
206	0	0	0	0	0	0	0	0	0
207	12084.43	702.58	702.58	293.05	17.04	17.04	11791.38	685.54	685.54
208	51312945.87	2983310.8	234234	1282823.65	74582.77	525	50030122.22	2908728.03	233709
209	226388.04	13162.09	55	61124.77	3553.76	3553.76	165263.27	9608.33	-3498.76
210	0	0	0	0	0	0	0	0	0
211	-172000	-10000	-10000	-32680	-1900	-1900	-139320	-8100	-8100
212	0	0	0	0	0	0	0	0	0
213	3499716.59	203471.9	203471.9	87492.91	5086.8	5086.8	3412223.68	198385.1	198385.1
214	7938035552	461513694.9	6666	198450888.8	11537842.37	233	7739584663	449975852.5	6433
215	505854497	29410145.18	22222	12646362.43	735253.63	55	493208134.6	28674891.55	22167
216	0	0	0	0	0	0	0	0	0
217	0	0	0	0	0	0	0	0	0
218	0	0	0	0	0	0	0	0	0
219	0	0	0	0	0	0	0	0	0
220	0	0	0	0	0	0	0	0	0

[AshRez · Accepted solution]

Check this out

[ntakeda]

@ThankForYourHelp 

You can achieve this by using a combination of the Transpose and Crosstab tools.

I have created a sample workflow for reference, so please check it.

The workflow includes the following steps, but you may need to remove them if they are unnecessary:

• In the Crosstab tool, fields are arranged in alphabetical order by default. To prevent this, the field names have been modified in Select tool(e.g., "Net De" → "1 Net De").
• The final tool restores the original names (e.g., "1 Net De" → "Net De").

I hope this helps!

[ThankForYourHelp]

hi @AshRez,

is there a way to make the column order more dynamic? I will have more and more columns and change all the names in select tool its not dynamic enough. Ending column should always at the end with a more dynamic tool instead change the order in select tool.

Thank you for your help

[AshRez]

You have the two options to try:

1- Dynamic Select Tool: https://help.alteryx.com/current/en/designer/tools/developer/dynamic-select-tool.html

2- Dynamic Rename Tool:  https://help.alteryx.com/current/en/designer/tools/developer/dynamic-rename-tool.html 

Notice that Dynamic Rename tool was provided to you as an option by another respond by "ntakeda"