Creating a new Sub-Cat record

[Paul1]

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

[JohnJPS · Accepted solution]

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!

[Paul1]

Thanks John,

That's great, looks like you are nearly there at the 2nd filter - I am getting an error at the crosstab however (i.e. saying "at least one crosstab method must be specified" - see attached.) - what is the purpsoe of the cross tab exactly is it to allow for the total rows to be deselected in the select tool that follwos? Tks again for your help Paul

[JohnJPS · Accepted solution]

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.

[Paul1]

Thanks John That is all great. The workflow yuo did for me was 99.9% there just before the crostab, after that I added filter tool and a startswith function to remove some of the rows I did'nt need and basically I wa there - thanks again for your help I really appreciate it - cheers paul