Creating a new Sub-Cat record

Question

Hi

I receive an excel file monthly where the basic format is consistent but the number of records can change from month to month. The file has product subcategories laid out one by one, I want to add a column for sub-category and convert to a flat file

The main issue is the number of records can vary per sub-category by month so I can’t use a record ID, Attached is an example of what I am trying to achieve, would appreciate any suggestions. – Tks

Create Sub Cat Field.xlsx

JohnJPS · Accepted Answer

I've attached a workflow that produces the desired output. You may have to adjust how it ties into your actual data.

Basically I use a MultiRow Formula to determine Sub-Category, and a Cross-Tab to do the little pivot. All other tools are largely just for cosmetic purposes.

Hope that helps!

JohnJPS · Accepted Answer

With "at least one crosstab method must be specified" they mean, in the Config Panel, towards the bottom, there are checkboxes with things like ":Concatenate", "First", "Last", etc... one of those needs to be selected.  In this specific situation they should all return the same thing since only one item matches in the cross tab.... but I'll usually select "Concatenate" since it also serves as a sort of error check: if you get a couple items then you know to have a closer look at the data.

Anyway, it's a little tricky to explain the Cross Tab tool, but one thing you can do is find it on the tool palette, single click it, and then choose to view the One-Tool example: they do a really great job of explaining it there.  That, combined with my sample workflow will hopefully give you good insight into how it does its job.