Hi all,
I have record strings, separated by spaces, but with differing numbers of decimal data points. For example:
Row 1: 0 3.546 0 5.678
Row 2: 7.34 0 5.91
Is there a way to create a dynamic RegEx statement (or TtoC statement) which would return a data point individually within a column? Ideally, if row 1 contained more data points than row 2, the empty row 2 columns would be returned as null values. The output would look like:
RegExOut1 | RegExOut2 | RegExOut3 | RegExOut4 | |
Row 1 | 0 | 3.546 | 0 | 5.678 |
Row 2 | 7.34 | 0 | 5.91 | [Null] |
I'm struggling to write a simplified expression without creating significant redundancies in the RegEx statement, ie
(\d+\.?\d*)\s(\d+\.?\d*)\s(\d+\.?\d*)\s.....
Thanks!
Mike
Solved! Go to Solution.
Hi @mmvcans
Here's one way to do this dynamically.
- Add a Record ID
- Use Text to columns with the delimiter as \s (space) and splitting to rows
- Use a Tile Tool on Unique Value as Record ID
- Use the Cross-Tab Tool to put this back into columns, using Tile_SequenceNum as the column names.
Cheers,
@Thableaus - worked like a charm; thanks for the quick response!