Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

Pivot Table Tabular view with all row labels

8 - Asteroid

Hello all, I have a data that does not follow any specific pattern of naming conventions. However, I have been assigned with the task to report this data into a Pivot Table which must have column labels repeated for unique Department Unit Value. See input data below


Emp NameEmp IDBusiness NameDept. NameCost center name
A2923198ABCMNO USA42551
B9600319DEFPQR CDA99349
C9014326GHISTU USA50420
D6209887ABCMNO USA46630
E8676261ABCVWX CDA35198
F7627931DEFPQR USA99674
G8857217DEFPQR CANADA16352
H4512643DEFYZA CDA22350
I9388900ABCMNO CDA83937
J7549823GHISTU CDA21611
K9215953JKLBCD USA54332
L4652102JKLEFG USA16797


The output data must look something like below


Business NameDept. NameCountry


Please note that for Business Name "DEF" the dept. name of PQR CDA and PQR Canada represent the same Dept. Name.

11 - Bolide

Hi @ankitsingh2063 ,

You could use RegEx or TextToColumns tools to separate your department name from your country, then either use a lookup table or a formula to clean up your country names. The best option for that will depend on how much variation you have in your data (is it just CANADA that comes across as a full country name? Does USA ever get formatted incorrectly? Are there more than two countries?). I attached an example that you can tweak based on your specific needs.

8 - Asteroid

Thank you for your quick response @kayers . This certainly helps in resolving the 1st part of the problem. As for the 2nd part, as you can see in the output data that the number of rows is only 10 instead of 12 which means that only the unique combinations of Business Name and Dept. Name goes through to the output.


Is there a shorter way to resolve this rather than going for applying a Unique tool on Concatenated code of Business Name and Dept?

Alteryx Certified Partner
Alteryx Certified Partner

Hi @ankitsingh2063 


Here's one possible solution:




- Use Text to Columns to split Dept Name from Country by \s (space)

- Use the Select tool to bring only desired fields and change the names of the Fields

- Sort by Business Name

- Use Formula Tool to correct the "CANADA" situation (using an IF condition)

- Use the Unique Tool to bring the Unique combinations


Is this suitable?


WF attached.



8 - Asteroid

Thanks @Thableaus and @kayers this resolves it. Just a follow up question. What if my Dept. Name is a phrase (combination of Words and last word representing country) - how do I extract only the last word (country) from this phrase into another column - For example:

Dept. Name
abc def ghi CDA


I need to extract CDA into a separate column

Dept. NameCountry
abc def ghiCDA


Thanks in Advance

Alteryx Certified Partner
Alteryx Certified Partner



In this case you can use the Regex Tool in Parse Mode instead of the Text to Columns tool.







The Regex expression divides the field in two columns:




(.*) - any character 0 or more times

\s - the last "space"

(.*) - any character 0 or more times in the 2nd column


So basically the last space is the divider. This is due to the REGEX mechanics of the (*) character, which is greedy. I recommend you to learn more about regex in this website:


WF attached.



8 - Asteroid



This community never ceases to amaze me.