Trouble breaking a row up into multiple rows with a variable amount of splits needed.

Question

I am receiving data where a transaction may have multiple products in a single row. I would like to break this up into multiple rows. The cherry on top is theoretically there could be any number of products in a single transactions.

Example of provided data:

TransactionProduct(s)Product Price 1Product Price 2Total Product PriceXYZA, B3070100

Desired outcome:

TransactionProductProduct PriceXYZA30XYZB70

I am kind of at a loss with this one. Any ideas? Am I over thinking this? And to my point above, there could be 1 product, 2, 3, 4, etc.

Thanks in advanced!

jrlindem · Accepted Answer

Hey @joelametz , yes you can certainly do this and dynamically.  Here's my example and attached workflow solution.

Assumptions:

* So long as your "Product Price" fields are named exactly as you have them (regardless of how many) and they contain a number digit after that string pattern
* The Product(s) are always going to be in the corresponding order as the Product Price

…then this solution will work and scale with no issue!

Take a look (above and attached) and hopefully this helps you get to where you need to be.  Of course it’ll require some adaptation but the concept is there.

Cheers,  -Jay

Hsandness · Accepted Answer

Hi @joelametz ,

This workflow could be a start.

1. Only select the Transaction and Product(s) column.

2. Split product to rows using the Columns to Split tool and selecting the Split to rows option with a comma as the delimiter.

3. Create a Transaction Record ID column to create a record ID per each product in the transaction.

4. In the bottom path of the workflow, only select columns with Transaction, Product Price, or Product(s) in the column name.

5. Transpose those results using the Transaction field as your key and the Product Price # columns as your value filed.

6. Join the data from step 5 onto the data from step 3 and you have the end result you need.