Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

not really tabular layout

kevind0718
7 - Meteor

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

8 REPLIES 8
kevind0718
7 - Meteor

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

 The counts are all in one column

binuacs
20 - Arcturus

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

Carolyn
11 - Bolide

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

 

2024-09-05_15-38-34.png

 

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

 

2024-09-05_15-40-15.png

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
13 - Pulsar

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:

Screenshot.png

 

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!

In this series we answer questions from the Alteryx Community Forum to demonstrate how to frame problems and approach solving them using Designer Desktop. Flatten Nested Data Tool: https://community.alteryx.com/t5/Community-Gallery/Flatten-Nested-Data-Tool/ta-p/1315202 Link to Question: ...
Carolyn
11 - Bolide

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

Thanks Bolide

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

 

KD

kevind0718
7 - Meteor

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
13 - Pulsar

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

Labels