Table Formula with changing number of Fields.

[Johnnyt3883]

Hi I am new to Alteryx and would like to understand its capabilities,

I have the following table,

Name	Field 2	Field 3	Field 4	Field 5	Field X
A	1	8	12	2	3
B	3	11	14	8	7
C	7	1	7	10	13
D	10	13	3	4	8
Y	24	7	1	3	5

Where the number of fields changes often depending on dataset I am looking at and is very large.

Number of Rows Y is also very large and changing between targeted datasets,

Is it possible to have the output be values be a % of the largest number in the field, if so what would the tool/ macro I would use,

the output of the example would be as follows  

Name	Field 2	Field 3	Field 4	Field 5	Field X
A	=1/24	=8/13	=12/14	2/10	=3/13
B	=3/24	=11/13	=14/14	8/10	=7/13
C	=7/24	=1/13	=7/14	10/10	=13/13
D	=10/24	=13/13	=3/14	4/10	=8/13
Y	=24/24	=7/13	=1/14	3/10	=5/13

Thanks in advance,

Jonathan :)

[binuacs]

@Johnnyt3883 

[mceleavey]

Hi @Johnnyt3883 ,

To explain what @binuacs has done here and the functionality used, the first step is to pivot the data to get all values into a single column. This is done using the Transpose tool, and it pivots the data around a group field, in this case the Name column:

This gives the following format:

As you can see, all values are now in a single column.

The next step is to use a Summarize tool to determine the Max number for each field:

Which gives us the following values:

This can then be joined back to the pivoted data using the field Name2, which effectively provides the Max Value for each field:

We can then simply use a formula tool provide the calculations:

Which provides the following:

Then, we simply pivot the data back, but substituting the original value with the new calculated value.

This is done using the Crosstab tool:

And we end up with the following result:

The primary tools used in this workflow are the Transpose and Crosstab tools.

You can find out more about these tools here:

Transpose

Crosstab

I hope this helps,

M.

[binuacs]

@mceleavey Thanks for taking time and explaining each steps.

[mceleavey]

[Qiu]

@mceleavey @binuacs 
nice workflow and documentation!👍

[Johnnyt3883]

Thank you @mceleavey @binuacs @Qiu for responding, great solution,

exactly what I was looking for,

Thanks again :)