Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Split field name into mulitple fields (inconsistent string length)

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.

1 Solution

Accepted Solutions

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 ';'

View solution in original post

6 Replies

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 ';'

View solution in original post

Not applicable

Hi Colin,

Upload some QVW with sample data . That is much better

BR

Vishi

MVP & Luminary
MVP & Luminary

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?

Master III
Master III

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

];

Contributor II
Contributor II

Thank you very much Avinash.

Subfield worked perfectly.

Much appreciated.