grouping and renaming.

[prpatel]

Hello all:

I don't know the exact syntax or name of what I want to do, but I want to take a table like this...

Group	In	Out
A	5	1
B	6	1
C	3	5
D	5	2
E	5	7

and convert it to a table like this...

Group	In	Out
A	24	16

in this case, I have a 25000 row dataset where the group (in my case this is Organization) has been identified in different ways (either as "Q.A." or "Quality" or "QA" or "Qual").  What I wish to do is to group them all and call it "QA" and have all their rows sum up.  I have seen this feature in Tableau, and was wondering how to do it in Alteryx to minimize my reliance on Tableau (primarily to speed up my Tableau visualizations).

Any thoughts?

-Tesh.

[binuacs · Accepted solution]

@prpatel One option is to add a column Group using formula tool and assign a variable ‘QA’ 

summarise tool group by the column Group and take sum of the other two columns

[AngelosPachis · Accepted solution]

Hi @prpatel ,

Alteryx doesn't have a native way to group values like Tableau has. What you can do is either use a formula tool to just rename groups A, B, C, D and E to just A and then summarize your values

OR

since all values under the group column will have to become A (or in your case "QA") then you can first use a summarize tool to find the sum of Ins and Outs and the use a formula tool to create the [Group] column

Hope that helps,

Angelos

[prpatel]

@binuacs and @AngelosPachis ....Thank you for your responses.  I'll use the formula tool to output the grouped name (in my case QA) by using an if statement such that...

..."  If [group] = "A" or "B" or "C" or "D" or "E" then "A" else "A" endif...

I think this will work...

-Tesh.

[AngelosPachis]

@prpatel a correct IF statement will have the following syntax in our case:

IF [group]="A" 

OR [group]="B" 

OR [group]="C" 

OR [group]="D" 

OR [group]="E" THEN "A"

ELSE "A"

ENDIF

Another way is an IN statement which is like a multiple OR, so

IF [group] IN ("A","B","C","D","E") THEN "A" ELSE "A" ENDIF

Nonetheless, since the result of the IF statement will always be "A", that makes the use of an IF statement redundant/. So you can just use a formula tool to change the values into the [group] column into just "A".

Hope that helps,

Angelos