Multi Row Combination/Parsing Problem

Question

I have a serial number field with data that spans multiple rows for the same item, that ultimately I am trying to parse into 1 serial number value per record.

Details of the data and fields are below:

* All of the associated records are identified together with another field value ("Product" in sample data).
* All values for the serial number field are numeric.
* All values in the field on each record are separated by a space.
* The field is 15 characters in length, however, the values wrap to the next record at various points (the whole field is not filled with characters before wrapping to the next record).
* Not all values for all records have the same length (some are single digit values ranging up to 5 digit values.)
* Not all values for an item set of records are the same length (some values may be 1 digit and others are 2 digits, etc.).
* Not all records for a product contain values in the serial number field (i.e. first two records have multiple values while remaining records for same product are Null.).
* Input serial number field currently formatted as a String and can remain a string or change to number.

Sample Input and desired output files are attached.  Screenshot shown is to view the sample input data file as it shows in my flow in the Input tool (with Null values).

Thanks for your help.

Desired Output Data File.csv

Sample Input Data File.csv

FrederikE · Accepted Answer

Hey @Drvt6713,

I see. I'd probably add an adjustment part that is able to fix it when something goes wrong. I have attached an example that splits off strings that are twice as long (chars) as the mode (e.g. normal) for this product. You could similarly also build a logic that accounts for the fact that the numbers are in a row (if that's not just the dummy data).

Drvt6713 · Accepted Answer

Hi @FrederikE ,

This will work.  I already have the S/N length in another field so can build off that.

Great job!

Thanks for the solution.