topic Re: Parsing field data with Subfield() in QlikView

Parsing field data with Subfield()

leocattqv — Fri, 13 Apr 2018 18:39:11 GMT

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

Re: Parsing field data with Subfield()

sunny_talwar — Fri, 13 Apr 2018 18:46:29 GMT

Try this without the 1

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

Re: Parsing field data with Subfield()

Peter_Cammaert — Fri, 13 Apr 2018 18:54:14 GMT

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

Re: Parsing field data with Subfield()

leocattqv — Fri, 13 Apr 2018 20:05:40 GMT

it worked, thank you very much

thanks

-L