Split field name into mulitple fields (inconsistent string length)

[Anonymous]

Hi all,

I have a field called 'Title' which is basically a long string which I want to split up and label as a number of different fields in the load script:

Package Type

Client Name

Country

Job Number

PO Number

Step

The string is in the form of a URL which comes in 3 types with all of the fields separated by '\' or '/' (in the case of Type 3):

Type 1:

Package Type: \\Link Name\Folder\Client Name\Job Number\Process\Step\Country\PO Number\Source\Job Number-PO Number.fileextension

Type 2:

Package Type: \\Link Name\Master Folder\Folder\Sub-Folder\Client Name\Job Number\Process\Step\Country\PO Number\Source\Job Number-PO Number.fileextension

Type 3:

Package Type: ////System/Tool/Tool/Data/Log Number/Log ID/Upload Type/PO Number/Timestamp/Text-Job Number-PO Number.fileextension

The first two types are downloads  which is why they are similar in structure, with just a couple of additional folder fields in the string for Type 2. they will have the same Package Type.

Type 3 is a download so the Package Type will differ, but it is linked to the first two by PO Number.

The strings all come in a single list as one field.

Given that the string lengths can be inconsistent, is there a way to cut them to define multiple fields in the script? I have thought about using the number of backslashes or forwardslashes, which will always be consistent based on the Package Type.

I have tried using the Mid function, but as the names of the internal fields can differ in length, it won't work consistently.

Any suggestions would be appreciated.

Thanks very much.

[avinashelite · Accepted solution]

use

subfield function it will work

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s.

In its two-parameter version, the subfield function generates one record for each substring that can be taken from a larger string s with the delimiter 'delimiter'. If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated.

Examples:

(For three parameters)

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ';' ,1) returns NULL if S is an empty string

subfield(S, ';' ,1) returns an empty string if S is ';'

[#3]

Hi Colin,

Upload some QVW with sample data . That is much better

BR

Vishi

[jagan]

Hi,

You can try SubField() to split the string into multiple values/fields. If possible can you attach sample data and your expected output.

Regards,

Jagan.

[robert_mika]

Which part of the String  is the Package Type?

[sasiparupudi1]

You could use a combination of subfield and replace functions to get to your results.

Please try the following script

HTH

LOAD

PackageType,

Value,

SubStringCount(Value2,'|') as x,

if (SubStringCount(Value2,'|')=11,SubField(Value2,'|',5),if(SubStringCount(Value2,'|')=13,SubField(Value2,'|',7),'')) as ClientName,

if (SubStringCount(Value2,'|')=11,SubField(Value2,'|',9),if(SubStringCount(Value2,'|')=13,SubField(Value2,'|',11),'')) as Country,

if (SubStringCount(Value2,'|')=11,SubField(Value2,'|',10),if(SubStringCount(Value2,'|')=13,SubField(Value2,'|',12),SubField(Value2,'|',9))) as PONumber,

if (SubStringCount(Value2,'|')=11,subfield(SubField(Value2,'|',12),'-',1),if(SubStringCount(Value2,'|')=13,subfield(SubField(Value2,'|',14),'-',1),subfield(SubField(Value2,'|',11),'-',2))) as JobNumber,

Value2;

LOAD

Value,

SubField(Value,':',1) as PackageType,

replace(replace(replace(SubField(Value,':',2) ,'/','|'),'\','|'),'||||','|') as Value2

Inline

[

Value

Package Type: \\Link Name\Folder\Client Name\Job Number\Process\Step\Country\PO Number\Source\Job Number-PO Number.fileextension

Package Type: \\Link Name\Master Folder\Folder\Sub-Folder\Client Name\Job Number\Process\Step\Country\PO Number\Source\Job Number-PO Number.fileextension

Package Type: ////System/Tool/Tool/Data/Log Number/Log ID/Upload Type/PO Number/Timestamp/Text-Job Number-PO Number.fileextension

];

[Anonymous]

Thank you very much Avinash.

Subfield worked perfectly.

Much appreciated.