Parsing field data with Subfield()

[leocattqv]

Hi Community,

I have a table that looks like this;

ID               Field1

ID1               12345,23456

ID2               13245,12345 23456

I would like to see the table look like this

ID               FieldTransformed

ID1               12345

ID1               23456

ID2               13245

ID2               12345

ID2                23456

I am attempting to use Subfield() here and it looks like this

SubField([ID],',',1) as [FieldTransformedl],

it doesnt seem to be working - anyone have any suggestions here?

I would appreciate it.

thanks

-L

[sunny_talwar]

Try this without the 1

SubField([ID],',') as [FieldTransformedl],

[Peter_Cammaert · Accepted solution]

Well, the solution may be at hand. The help page of SubField() talks about a 2-parameter version that in one sweep

• splits strings into substrings, and
• creates a new record for every substring.

The only problem that remains is that you seem to have different subfield separators. If the use of both comma's and spaces is consistent throughout your table data, then that isn't much of a problem either. Replace() comes to the rescue!

If we call your first table RawData, then this may do the job.

NewData: // Uses 2-parameter version of SubField to create new records

LOAD ID,

     SubField(Replace(FieldTransformed, ' ', ','), ',') AS FieldTransformed

RESIDENT RawData;

Best,

Peter

[leocattqv]

it worked, thank you very much

thanks

-L