Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ';'
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 ';'
Hi Colin,
Upload some QVW with sample data . That is much better
BR
Vishi
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.
Which part of the String is the Package Type?
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
];
Thank you very much Avinash.
Subfield worked perfectly.
Much appreciated.