Restructuring Data Set

[shelbybradley]

 

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!

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

[shelbybradley]

Got it - I misunderstood you.

Attached is the excel file.

[Luke_C]

Hi @shelbybradley 

Here's an example of this: 

1. Isolate the city name, will be appended at the end.
2. Isolate the 3 header rows, transpose, and turn into columns with IDs based on order
3. Isolate the data, since columns repeat they get appended with an ID (COGS2,COGS3, etc)
4. We can transpose and isolate those numbers that were appended to join up with the header IDs we create
5. Join data together and format how you need.

[shelbybradley]

Hi @Luke_C 

Thank you - I have a few follow up questions...

1. What are you doing exactly with the Multi-Row formula tool (in this particular case)?

2. How does the Make Columns tool work?

3. What is the Tile tool doing?

[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