Extract measurement Unit of Measure (UOM) to next column over to the right

1

I have data stored in a format where the column header is rather generic (Attribute Name 1, Attribute Name 2) - I have about 50 attribute fields like this. I'm trying to programattically (meaning not do it one attribute at a time) either add or extract the Unit of measure from those attributes that are some sort of dimension.

For example in this:

image
image977×458 8.79 KB

For those attribute values that are a number (or text stored as a number), note not all the attributes in the column are the same, I'd like to put 'in' for inches in the Attribute_UOM_XX column to the right of the value.

And for those values where they have the inch marks like 2", I'd like to remove the inch marks from the value and put 'in' in the Attribute_UOM_XX column to the right of the value. Note that here elsewhere in the data the inch marks could be CM, M, FT, ft, ' etc. It could be 2 CM, or 2cm.

Any help on how this might be accomplished without specifically writing a calculated field via regex 50 times would be appreciated.

2

Can you post a sample data file? It would be easier to answer :slight_smile:

I would unpivot the table first and then re-pivot it in a way that all attributes are in one column, and all units of measure are in an adjacent column. Then it would be much easier to make the necessary changes.

3

Sorry for the delayed reply as I was on leave - here's a sample file with some data with the inch marks, some without and in random spots within the columns.
Sample.xlsx (16.8 KB)

4

Here you go.

Notes:

  • Some tables and actions have useful annotations
  • The algorithm assumes that EXTERNAL_SYSTEM_ITEM_ID is unique. Otherwise, rows in the input dataset must be enumerated.

uom.morph (23.5 KB)

5

Sorry to bring this back up after much time - as I've been pulled to other projects. Your sample project pulls the UOM column out appropriatly...but what I'm asking is how to extract the UOM from the Attribute Value column and place it into the UOM column.

For example in the screenshot I posted or the sample file - pull the inch marks " out of the 2" from attribute_value_11 and put it in attribute_uom_11. So I'd end up with Attribute_Value_11 = 2 and then Attribute_uom_11 = "

The trick is as you can see the attribute_value_11 is not always a measurement, and it's not always inches, could be any unit of measurement. Below is a marked up image of what I'm trying to do...a poorly executed markup :slight_smile:

image
image981×454 23.3 KB