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 4Northern 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

not really tabular layout

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

Input

Tips and Tricks

Transformation

Accepted answers

ccanterman

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

All comments

kevind0718

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

The counts are all in one column

binu_acs

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

ccanterman

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!

Sample Workflow.yxmd

ccanterman

@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.