This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Alteryx Gallery is experiencing a problem in which system emails are not being sent out. As a result, if you are attempting to sign up for a new account, you may be unable to verify your email address. We are working to solve this as soon as possible and will remove this notice once resolved.
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
Cost center name
The output data must look something like below
Please note that for Business Name "DEF" the dept. name of PQR CDA and PQR Canada represent the same Dept. Name.
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.
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?
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: