Need help converting a text report into Alteryx!

[bilal0310]

I have a text report that is an input into our Alteryx and looking to format and align the data accordingly. I have attached a txt report and and excel with the desire look I'm trying to achieve. Any help would be appreciated.

[echuong1 · Accepted solution]

For the most part, your file is a fixed width text file, which makes it easy to parse. The difficulty comes in with cost-group, since it's on a different line.

I used a multi-row formula to identify the cost group lines and "drag them down" until I hit the next one. I then used a filter with a regex match function to identify lines that began with numbers (item number). I then used regex with a character count to delimit the data. I used a text to columns to identify the cost group value as well.

A data cleansing removed the extra spaces. A multi-field formula dynamically removed the commas in the numeric values and changed the data type. A select renamed appropriately, and a formula calculated unit cost.

Hope this helps!

[bilal0310]

Thank you, but apparently the workflow did not open for me since I have an older version of Alteryx. Would you be able to share the formula you used in the Regex Tool?

[echuong1]

The regex is simply a character count: (.{15})(.{16})(.{24})(.{6})(.{18})(.{10})(.{18})(.{16})(.{18})(.{9})(.{18})(.+)

Also, a workflow is simply XML, and you can change the version to open it. See instructions here:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Adjusting-Alteryx-Files-for-Differe... 

[bilal0310]

Thank you, you are a life saver !