Grouping Data Dynamically

[akvsachin]

Hi Folks,

I need assistance with dynamically grouping data based on user input while retaining all the records.
Attached is an example of the input for the workflow.

Looking forward to your suggestions.

Elaborating my confusion about the grouping process in this scenario. When a user inputs the field/column names, the Action Tool replaces the data in the Text Input Tool. However, I’m unsure how this setup would enable grouping, as the user would provide input like C1, C2, C3.

To parse this data, I assume we would need to use the Text To Columns Tool, followed by possibly using the Transpose Tool or Cross-Tab Tool to structure it into fields. But I’m struggling to understand how we would effectively group the data from the Input Data Tool based on this parsed information.

[binuacs]

@akvsachin can you provide a sample input file and expected output for better understanding of your requirements? 

[akvsachin]

Hi @binuacs 

Here, Attached is a similar example of dataset.

1st sheet is input and 2nd is most similar to expected output.

Although there I've used sort functions from excel.

In my task, It has been instated as Group-By Columns.

Group By Column(s) (Optional):
1. User will manually input column names
2. The user can provide more than one column per extract
Columns Seperated by Comma
3. If selected Alteryx will group the rows by column name from excel input file.
Grand Total Column (Optional):
1. The user will manually input the excel file column names that require a total calculated field
The user can provide more than one column per extract
Additional Sub Total (Optional):
1. If selected -
Alteryx will add a break within the report based on the user selected 'Group By Column(s)' Input Parameter filtering
Alteryx will add a new sub total calculated field for the columns provided under the 'Grand Total Column(s)' Input Parameter
2. If Not selected - Alteryx will not add a break within the report and will only provide a Grand Total.

[nagakavyasri]

@akvsachin Try if this can be helpful. Can give you idea on how to proceed.

[binuacs]

@akvsachin my understanding If the user input the field category then the report should be grouped based on category, if the user input the field Country then the report should be grouped by the country field, if the user select multiple fields like Category, Name and Country then the report should group based on these three fields? or am i missing something here?

[akvsachin]

you're not missing anything, that's correct.
and similarly Sub-Total and Grand Total would also be taken.

[PangHC · Accepted solution]

@akvsachin 
tons of knowledge for the interactive, xml and regex...

it use list tool (act as select tool), and use regex to mimic the xml in summarize tool (and sort tool).

it need to load file and the option will show later.

[akvsachin]

Hey @PangHC 

Great work, that solves the job.
But, In Alteryx, Is there any way to use textbox tool for this job.
As, List Tool or DropDown Tool won't successfully work in the environment we'll be running the analytic app.

[PangHC]

@akvsachin if in alteryx gallery, i think it should be fine. 

you can switch to textbox. the formula is similar.
just update $1 to "$1" for all the actions' formula. 

example:
<SummarizeField field=$1 action='x' rename=$1 /> 
<SummarizeField field="$1" action="x" rename="$1" /> 

[akvsachin]

Hi @PangHC ,

For Grouping/Sub-total/Grand-Total of multiple inputs, While entering text input like "Name, Animal, Amount"

Error shows up as *

Summarize: The field "Animal" is missing.

Summarize: The field "Amount" is missing.

It's only taking the first field/ one input as the input and other inputs as errors.