Grouping Data in a Fixed Width file

[Longhorns]

Hi All,

I am new to Alteryx and am looking for some help on how to best group data within a large fixed width file. I need to parse out the stacked data into unique groups based on the client identifier. In the source file each group of data is separated by a row only containing a "0" in the first column of the file. 

I used the multi formula tool to create an additional row that identifies each row that should be grouped together. 

I wasn't sure if there was a better way to do this as the source file can be very large. Also, is there way to write into the expression to output the Client # as the value in the additional row? I attached an example of the source file. Any assistance would be greatly appreciated.

[pedrodrfaria]

Hi @Longhorns 

I'm not sure how you need the output to be shaped as there was no indication for this. I was not sure if you needed to parse our all the variables into different columns or just needed to flag which rows represented which client.

I attached a WF that shows you how to flag each row for the respective client name.

If you need to parse all the data out to columns, you would probably be looking at Regex as a solution, which transforms this use case into something a little more complex.

Please let me know if this helps you.

Pedro.

[Longhorns]

Hi @pedrodrfaria

Thank you very much for the WF example detailing how to flag with the corresponding Client. This was very helpful!

I do want to shape the output so that all of the different variable data is parsed out and associated with the corresponding client # they are listed under in the source file. So I need to look into Regex as a solution for this type of scenario. 

[SeanAdams · Accepted solution]

Hey @Longhorns 

Given that you're relatively new to Alteryx, here's an article that may help with future posts: https://community.alteryx.com/t5/General-Discussions/Posting-for-fastest-possible-solution/td-p/6916...

This need to carry a value from a report header into the rows is quite common and has come up a few times in the weekly challenges (well worth doing these to build your skills and see how other people tackle them) https://community.alteryx.com/t5/*/bd-p/weeklychallenge 

@pedrodrfaria 's solution is the most common way of tackling this - using a multi-row formula to carry down a value (if isnull([Client]) then [Row-1:Client] else [Client] endif) .   This also comes up a lot in XML parsing.

I've updated @pedrodrfaria 's solution to parse out the client ID using a Regex; and then copy this down.

Resources:

• I find http://regex101.com very helpful to get the actual regex syntax right - it can be tricky.   
• Training here: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...
• There is a ton of info on regex on the internet more broadly
• multiple weekly challenges that you can solve with regex if you want to practice https://community.alteryx.com/t5/*/bd-p/weeklychallenge 

[Longhorns]

.@SeanAdams 

Thank you much for providing all of the resources and updating the WF. Very much appreciated.