Grouping Data in a Fixed Width file

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.

alteryxEX.txt

Datasets

Common Use Cases

Best Practices

Accepted answers

SeanAdams

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/69164

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

Grouping Data in a Fixed Width file.yxmd

All comments

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.

Grouping Data in a Fixed Width file.yxmd

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

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/69164

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

Grouping Data in a Fixed Width file.yxmd

Longhorns

.@SeanAdams

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280