community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Making Sense Out Of Horrible Spreadsheet

Fireball

Hello Alteryx Gurus - 

 

I've got a spreadsheet from the customer that fits the format of the attached document.  Basically this is how the customer has their scheduling data prepared, my job is to write a workflow that can digest it, and eventually reconcile back to me an Alteryx Ace, Customer name, address, and phone number.  It has merged cells sometimes, lots of empty cells, and instances where sometimes the address spans two cells, sometimes only one, with a resultant change in the location of the phone number.  I've been banging my head on this one a while and nothing is happening.  

 

Can anyone help?

Thanks.

brian

 

Asteroid

Does the Original document have the merged, empty, split cells or the output after your workflow? Can you also share some of your workflow? 

Fireball

This is a clone of an original document with names, addresses and phone numbers replaced to protect the innocent.  I do not need to output a file after the end of this, but I do need to know which person in column A (represented by JM and MF in my sample sheet), has which customers at which time slots, and what the phone number and address of those customers.  

 

I.e., I need to be able to know that Joe has a Random Customer in the second AM slot, and Happy Person B in the first pm slot, along with their address and phone numbers.  I need to know that Mark has Another Random Name in the first AM slot, and Last Entry in the first PM slot.  With data transformed in that manner, I could work on doing the rest of the tasksI need to perform.

 

My workflow as it exists now would probably jut give you a migraine.  I know it has given me one.  I have been able to identify min/max row numbers for a particular agent, and have sort of figured out how to pivot the entire world and identify phone numbers, though the possibility of columns for which there may never be data for is complicating things.

 

Thanks.

 

brian

Asteroid

I understand, i am kind of in the same boat as you with dealing with my team/company. Personally what i had to do was redesign my companies excel sheet across all of the teams. The format was different/horrible on most of them and was making the model 10x harder than it should be. If that inst an option, you could use the RegEx tool followed by a filter into a new field. The RegEx tool will identify the addresses using a formula (see attached) and then the filter would (it sounds like) a simple conditional statement. I would maybe filter based on 1 hour time slots (or 30 min time slots) and that would produce a separate field based on Employer, the customer for that time slot. After that you can join based on time or name of employee. Let me know if this helps, sorry its not a direct answer

 

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

 

Fireball

Hi!  

 

At this point, dealing with the customers to convince them to change formats may be a bigger headache than hacking through this.  I will evaluate your regex solution.  

 

Thank you!

brian

Asteroid

I completely understand. Again sorry i couldn't help you directly.

Highlighted
Asteroid

Ok so without doing the fun part for you... ;P

 

1. Open up the file and go to option 7 on the input tool - start on line 3

2. Use multi row to fill in each AlteryxAce field in with the line above it

3. Dynamic rename columns so their names are all different (not all AM)

4. Use transpose to split out each column, grouping by alteryxace.

5. Use regex to segment each type of field

   5.1 For phones use regex_match ^(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$

   5.2.1 For address vs name once phone is done, addresses will have numbers and names wont (yeah i know not 100% esp if they are company names)

   5.2.2 if 5.2.1 does not work then pull out a copy of the input (before multi row formula), rename columns the same way, and transpose with group by alteryx ace. These are the company names. Then join them to identify.

6. The order of the addresses I haven't thought through all the way but maybe you could search for key words or length to approximate?

7. Cross tab it all back with Type as the headers and grouping by alteryx ace and former column name.

 

 

Good luck! Hopefully this all makes sense.

Fireball

Thanks WillBowman!  I'm going to try this now and will report back. 

Fireball

That pretty much got me there.  Utilizing multi row to give me consistent ace names made the transpose to crosstab component make sense within my brain cells.  Thanks a lot. 

 

brian

Asteroid

Awesome! Glad I could help.

 

Will

Labels