not really tabular layout

[kevind0718]

Hello: 

I have received a data file with an annoying layout.  

The layout looks like the following:  

Eastern Division
	Fort Hamilition Section
		Gate C-2
			2021-05-21
				Passengar Vehicle - Contractor	2
				Truck - Military		3
	Parris Island Section
		Gate G-4
			2022-07-18
				Passengar Vehicle - Contractor	3
				Truck - Military		2
				Motorcycle			4
Northern Division
	Fort Edward Section
		Gate Q-11
			2023-09-07
				Truck - Contractor		5
				Bicycle			2
			2023-09-08
	Naval Sub - New London Section
Western Division
	Fort Jackson section
	Fort Drum Section

The division applies on till it changes. Same with the section, section applies until it changes.

Not too bad for a human.    How can I use Alteryx to clean this up?

I need rows containing  Division, Section, Gate, Date, vehicle, count

Any ideas I am stumped?

Thanks for your kind assistance

Kevin Duffy

[kevind0718]

ooops something went wrong with the posting. Example loaded wrong. 

 The counts are all in one column

[binuacs]

@kevind0718 Can you repost with the correct layout as well as expected output?

[Carolyn · Accepted solution]

You can use the Multi-Row Tool with logic that says "if <field> is empty, then take the value from <field> one row up. Otherwise, put the value that's in <field>". Do that for each of Division, Section, Gate, and Date (screenshot just shows Division & Section). 

 

Once they're all filled in, you can filter for where the Vehicle or the Count is not empty. That'll remove all the excess lines and just leave you with the now-filled in lines that show the Vehicle & Count

 

One thing to note - in my screenshot, I have a Multi-Field Tool with a TRIM formula. That's just because from my copy/paste, all the blanks came in as " " which was screwing up my formulas. I did a trim to remove the phantom spaces so I could use the IsEmpty formula in the Multi-Row

[CoG]

Here is my approach to solving this problem: https://youtu.be/aUdr53gdgts

Feel free to use this workflow, or... check out this Macro Tool on the Gallery I made to Flatten Nested Data:

Flatten Nested Data Tool - Alteryx Community

Wow... This was hard. You can just use Multi-Row Formula Tool over and over again, Grouping by all previous columns, cascading values downward. I wanted to see if a generalized workflow structure existed that could handle any depth, and I think I found a workable solution:

In the first part all extraneous nulls are removed, leaving behind a flattened "tabular" form of the data, but with missing records. In the second part those missing records are filled in.

Hope this helps and Happy Solving!

[Carolyn]

@CoG - That's a fun way to solve it! Especially with more columns or changing columns, that'll be a great way to solve it. Also good to know there's a macro. 

[kevind0718]

Thanks Bolide

You gave me the pointer I needed.  This worked just fine.

KD

[kevind0718]

CoG;

Thanks for all the effort you put into your solution.

You gave this lots of thought.

Neat generalized solution.

Just too complicated for my current issue.

KD

[CoG]

@kevind0718 

Sure Thing! This is a problem I have seen occur regularly in this forum.

Out of curiosity, did you try the Flatten Nested Data Tool from the gallery that I linked?