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

Making Sense Out Of Horrible Spreadsheet

brianscott
11 - Bolide

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

 

9 REPLIES 9
AustinRiggs94
8 - 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? 

brianscott
11 - Bolide

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

AustinRiggs94
8 - 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

 

brianscott
11 - Bolide

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

AustinRiggs94
8 - Asteroid

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

WillBowman
9 - Comet

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.

brianscott
11 - Bolide

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

brianscott
11 - Bolide

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

WillBowman
9 - Comet

Awesome! Glad I could help.

 

Will

Labels