Restructuring Data Set

Example data set.PNG

Attached is the data set in its initial form. I would like to restructure the data set into just a few columns with the most critical information (Sales Q, Sales per item, COGS, Inventory) in 1 column each and no longer repeating.

As you can see, Rows 1-3 contain the contextual information for the data columns. I need the information in the first three rows (specifically, City, Store Location, and Product) but I need them to be their own columns. But, I am having trouble converting them into columns because they sit right on top of rest of the data.

Thus , I would like to have the final output have the columns:

- City

- Estimated ship date (Column C)

- Store Location

- Product

- Sales Q.

- $ per item

- COGS

- Inventory

Please let me know if you can help.

Thanks!

Workflow

Datasets

Common Use Cases

Best Practices

Transformation

Join

Accepted answers

All comments

Luke_C

Any chance you can share the excel? There's a lot there to retype manually to help you.

shelbybradley

Well, I would like to do the "restructuring" in Alteryx. Reformatting is my use case.

Luke_C

@shelbybradley Understood, but we cannot use a screenshot as an input. This is definitely possible but trying to avoid retyping your screenshot to show you how. Take a look at the below thread that is a similar issue.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-Pivot-Data-with-Multiple-Column-Headers/td-p/443473

shelbybradley

Got it - I misunderstood you.

Attached is the excel file.

Fake data set.xlsx

Luke_C

Hi @shelbybradley

The multirow is flagging the information we want (location, product, included manager for good measure). Its flagging both the field name and the value under the assumption they are sequential after the transpose. This facilitates filtering on just the data we want
The make columns is separating the field name and value into separate columns. It's set to do 2. It takes the single column and puts the records into to columns, 1st record column 1, 2nd record column 2, 3rd record column 1, 4th record column 2, etc.
The tile tool is assigning a unique ID so we know how to associate the data. It also facilitates the cross tab. You'll see the tilesequence num can be used to associate locations and products

shelbybradley

@Luke_C

Thank you!

I ran into another issue...

I have another data set that has many more rows of data than the previous one (same exact format). Eventually, Alteryx begins naming the duplicate columns Sales Quantity9_2, COGS9_2, Inventory9_2, etc...... Is there a way to alter the formula tool which calculates ID to adapt to the change is formatting?

The problem I am running into is that the ID formula:

ID =

if isinteger(right([Name],1))
then tonumber(Right([Name], 1))
else 1
endif

does not work for those columns ending in COGS9_5. As an example, the resulting column name becomes COGS9_ instead of COGS, and the ID number becomes 5, which is inaccurate.

I have attached another excel with more columns if that helps.

Thanks,

Shelby

Fake data set.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703