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

Excel formatting - unstructured data

Kirstyp
8 - Asteroid

Hi

 

I have the attached excel workbook and it's not in a great structure,  I need to format it to remove unnecessary columns and give the data headers and transpose etc. Can anyone help me please?

 

There is a tab in the sheet showing the excel unstructured data, then another example of how I would like it to look (example tab) . Within the example tab I have only shown the first few fields, I would still require all data on the unstructured sheet.

 

Many Thanks in advance.

4 REPLIES 4
darryl5280
10 - Fireball

The Keys to parsing in unstructured data:

  • To first Assign each row a "Record ID", that helps with how to treat each row.
  • Get rid of the blank rows.
  • Use the “Generate Rows” tool to put each Description and Value on a single row, when there are multiple Descriptions and Values on a single row.
  • Use the “Multi-Row Formula” tool with the “Switch” function to get Descriptions and Values from upcoming rows.

The attached workflow I started for you should be a good jump on point to continue with parsing the remaining rows.

Unstrured Data Workflow.jpg

Kirstyp
8 - Asteroid

Great, thanks for this I will take a look :)

Kirstyp
8 - Asteroid

this is great, thanks.

 

I'm struggling to get my transpose to work at the end?  I basically want to be able to 'anchor' (so to speak) the name and date of FA, then having the remaining columns as headers with the data in the same row as the name.  (example with dummy data below)

 

So final output is; 

Name          Date of FA     income support    benefit    job seeker   etc         etc

Jo Bloggs    14.01.2018     10.00                     100.00     200.00       300.00  400.00

darryl5280
10 - Fireball

Made a new version of  the workflow with having only 1 row with many fields.  And the key tool used now is the "CrossTab" Tool which is much easier to understand than the "Tranpose" tool.  Good luck with the rest of the fields and I think you can copy sections of code around to process more of the rows.  Attached is version 2 of the Workflow with just one row with many fields.

Labels