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

Pivot Table Tabular view with all row labels

ankitsingh2063
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
ABCMNOUSA
ABCVWXCDA
ABCMNOCDA
DEFPQRCDA
DEFPQRUSA
DEFYZACDA
GHISTUUSA
GHISTUCDA
JKLBCDUSA
JKLEFGUSA

 

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

6 REPLIES 6
kelsey_kincaid
12 - Quasar

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.

ankitsingh2063
8 - Asteroid

Thank you for your quick response @kelsey_kincaid . 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?

Thableaus
17 - Castor
17 - Castor

Hi @ankitsingh2063 

 

Here's one possible solution:

 

DeptWorkflow.PNG

 

- 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.

 

Cheers,

ankitsingh2063
8 - Asteroid

Thanks @Thableaus and @kelsey_kincaid 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

Thableaus
17 - Castor
17 - Castor

@ankitsingh2063 

 

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

 

regexparsing.PNG

 

regex1.PNG

 

 

The Regex expression divides the field in two columns:

 

(.*)\s(.*)

 

(.*) - 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:

https://www.rexegg.com/

 

WF attached.

 

Cheers, 

ankitsingh2063
8 - Asteroid

Perfect! 

 

This community never ceases to amaze me.

Labels