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 nameA2923198ABCMNO USA42551B9600319DEFPQR CDA99349C9014326GHISTU USA50420D6209887ABCMNO USA46630E8676261ABCVWX CDA35198F7627931DEFPQR USA99674G8857217DEFPQR CANADA16352H4512643DEFYZA CDA22350I9388900ABCMNO CDA83937J7549823GHISTU CDA21611K9215953JKLBCD USA54332L4652102JKLEFG USA16797 The output data must look something like below Business NameDept. NameCountryABCMNOUSAABCVWXCDAABCMNOCDADEFPQRCDADEFPQRUSADEFYZACDAGHISTUUSAGHISTUCDAJKLBCDUSAJKLEFGUSA Please note that for Business Name "DEF" the dept. name of PQR CDA and PQR Canada represent the same Dept. Name.

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.

Reporting

Accepted answers

Thableaus

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,

SimpleWorkflow.yxmd

Thableaus

@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,

SimpleWorkflow.yxmd

All comments

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.

ParseDeptName071119.yxmd

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

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,

SimpleWorkflow.yxmd

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317