Pivot Table Tabular view with all row labels

[ankitsingh2063]

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 Name	Emp ID	Business Name	Dept. Name	Cost center name
A	2923198	ABC	MNO USA	42551
B	9600319	DEF	PQR CDA	99349
C	9014326	GHI	STU USA	50420
D	6209887	ABC	MNO USA	46630
E	8676261	ABC	VWX CDA	35198
F	7627931	DEF	PQR USA	99674
G	8857217	DEF	PQR CANADA	16352
H	4512643	DEF	YZA CDA	22350
I	9388900	ABC	MNO CDA	83937
J	7549823	GHI	STU CDA	21611
K	9215953	JKL	BCD USA	54332
L	4652102	JKL	EFG USA	16797

The output data must look something like below

Business Name	Dept. Name	Country
ABC	MNO	USA
ABC	VWX	CDA
ABC	MNO	CDA
DEF	PQR	CDA
DEF	PQR	USA
DEF	YZA	CDA
GHI	STU	USA
GHI	STU	CDA
JKL	BCD	USA
JKL	EFG	USA

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

[kelsey_kincaid]

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]

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 · Accepted solution]

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.

Cheers,

[ankitsingh2063]

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. Name	Country
abc def ghi	CDA

Thanks in Advance

[Thableaus · Accepted solution]

@ankitsingh2063 

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:

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

Perfect! 

This community never ceases to amaze me.