If you are like me, you probably use SubField a lot. I had used it for years before I stumbled across one of its most powerful features in someone else’s code. If you do not provide the position parameter, it will expand a record for each value.
I’ve worked with a few older databases that liked to store arrays within a single column. This is a perfect example of where it comes in handy.
Data: Load RowNo() as ID, Replace(Replace(Qty,'(','['),')',']') as Qty Inline [ Qty (10,12,15,20) (25,25,25,25) (1080,720,2,3) ](delimiter is \t); Expanded: Load RowNo() as LineID, ID, Qty, SubField(PurgeChar(Qty,''),',') as Quantity Resident Data; Drop Table Data;
Which results in: