Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Aggregation on multiple columns

sriniprad08
11 - Bolide

Hi Team,

 

I need help. Please find below the sample. 

C CategoryCategory2018-012018-022018-03
AAC322,165304,638299,908
BAC461,108409,994779,603
CAC1,449,9441,751,5321,763,404
DAC21,21523,78726,406
ABC310,499310,499316,931
BBC426,705426,705426,705
CBC1,362,6591,362,6591,362,659
DBC24,75524,755

24,755

 

 

the output will be- The calcuation is (AC/BC) for each C category. 

OUTPUT  
C Category2018-012018-022018-03
A1.0375730.9811240.946288
B1.0806250.9608371.827029
C1.0640551.2853781.294091
D0.857010.9609051.066685

 

thanks for the help in advance

11 REPLIES 11
jamielaird
14 - Magnetar

Hi @sriniprad08 ,

 

Here is a workflow that achieves this outcome:

 

jamielaird_0-1615288474439.png

 

Let me know if you have any questions

sriniprad08
11 - Bolide

Thank you so much @jamielaird . Really helpful.

sriniprad08
11 - Bolide

Hi @jamielaird ,

 

Thank you for all the support. I need help. When i tried to run with the larger data, i am getting below warning and it's not calculating correctly. Please let me know your thoughts,

 

sriniprad08_0-1615476325463.png

 

Thanks ,

Sri

jamielaird
14 - Magnetar

Hi @sriniprad08 

 

The numbers that are generating error messages are differently structured from the examples you originally posted. Something might have gone wrong further upstream in your workflow or in your source data.

 

Can you provide some examples of the "Actual" values in the larger dataset that are causing you problems?

sriniprad08
11 - Bolide

Hi @jamielaird ,

 

Thank you for the reply. Yes i think you are right . data has changed. Please find attached the sample

 

RegionFC2012-012012-02
ES AC  $         134,343.04 $   1,175,400.32
ES AC  $   33,434,324.03 $ 10,255,362.21
ES AC  $   69,834,343.43 $      938,156.36
ES AC  $     6,138,121.98 $   8,367,451.48
ES AC  $     1,141,218.62 $      734,581.36
ES AC  $     3,665,372.91 $   5,271,751.13
ES AC  $     1,111,895.48 $   1,410,534.47
ES AC  $   16,754,975.47 $ 21,601,329.04
ES BC  $     1,484,531.69-$      283,764.02
ES BC  $   12,029,195.97-$   1,846,354.54
ES BC  $     1,714,693.56-$   1,111,740.28
ES BC  $     7,446,809.78-$   2,323,129.63
ES BC  $     1,210,998.10-$   2,363,167.04
ES BC  $     4,240,395.68-$   3,266,488.35
ES BC  $     1,350,858.93-$      403,628.26
ES BC  $   18,786,938.38-$   2,738,109.35

 

Please let me know your thoughts,

 

Thanks and Regards,

Sri

jamielaird
14 - Magnetar

Hi @sriniprad08 ,

 

Thanks for sharing a second set of sample data. As you have noted, the structure of the data is different from your first set of sample data, which means the original solution won't work.


Using BOTH the first and second sets of sample data I have developed a more dynamic solution. There are some basic requirements for the data that you feed into this:

 

  1. Must contain a Region column
  2. Must contain an FC column, with a value of AC or BC
  3. Must contain values in one column per month
  4. Ordering or records in the AC block must match the order of the BC block

The basic steps within the workflow are:

 

  1. Record ID added for each row in the AC and BC blocks
  2. Remove whitespace, symbols and characters
  3. Transpose the monthly data from rows to columns
  4. Join the AC and BC blocks using the Record ID from step 1
  5. Calculate AC/BC for each record
  6. Cross tab the data into a table, grouping by Region and Record ID
  7. Drop unnecessary fields

jamielaird_0-1615832832841.png

As a next step I would recommend you test the solution against a wider set of input data. If your data varies significantly, you will always have to go through a process of standardizing it before you can expect a pre-built set of workflow steps to give you the desired output.

 

This would be a great use case for a macro, but I don't want to overcomplicate things by going into that now.

 

Hope this was helpful!

sriniprad08
11 - Bolide

Hi @jamielaird ,

 

Thank you so much. Really appreciate all the support. I will definitely try with the different set.

Also if you can share your thoughts on the Macro that would be very helpful to work on for future.

 

Cheers,

Sr

sriniprad08
11 - Bolide

Hi @jamielaird ,

 

You have included multi row formula. Can you explain the reason for this? I would like to learn.

 

cheers,

Sr

jamielaird
14 - Magnetar

Hey @sriniprad08 ,

 

I've attached an annotated image explaining how the Multi-Row Formula tool is used in this instance to number each group of records:

 

jamielaird_0-1615907722560.png

 

To learn more, I suggest checking out the Tool Mastery blog on the Multi-Row Formula:

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Row-Formula/ta-p...

Labels