topic Re: Substring function in Qlikview ? in QlikView

Substring function in Qlikview ?

Mon, 14 Dec 2015 15:45:55 GMT

Hi,

We are concatenating two fields and data after concatenation is like below using Pipe delimiter.

Brideport||CT

Boston||MA

Albany||NY

we would need to extract City and State from this above data ?

How can we Extract City which is all the words before Pipe and

Extract State which is everything after Pipe ?

do we have Substring Function or similar function like Substring in QV?

Any help would be appreciated.

Re: Substring function in Qlikview ?

sunny_talwar — Mon, 14 Dec 2015 15:47:15 GMT

Use SubField Function

For City -> SubField(FieldName, '|', 1)

For State -> SubField(FieldName, '|', 2)

Re: Substring function in Qlikview ?

Mon, 14 Dec 2015 15:59:17 GMT

Thanks Sunny.

can you please explain this function and it's parameter ?

what is 1 and 2 in the example ?

why it using single pipe - | instead of || ?

Re: Substring function in Qlikview ?

sunny_talwar — Mon, 14 Dec 2015 16:04:30 GMT

Look on QlikView's help page:

QlikView ‒ SubField - script and chart function‌

and you are right, it should use || (douple pipe). I did not see two pipes initially:

For City -> SubField(FieldName, '||', 1)

For State -> SubField(FieldName, '||', 2)

Re: Substring function in Qlikview ?

JonnyPoole — Mon, 14 Dec 2015 16:39:41 GMT

I like subfield for this too. If you are familar with mid() from other languages , this may look more familiar to you:

mid( FieldName, index(FieldName,'||') + 2 )

.. it finds the postion of '||' , adds 2 to the character position, and takes everything to the right of that position

Re: Substring function in Qlikview ?

engishfaque — Tue, 15 Dec 2015 03:27:47 GMT

Dear Haileydesai,

Kindly find attached file, I'm sure you are looking for that one.

Kind regards,

Ishfaque Ahmed

Re: Substring function in Qlikview ?

HirisH_V7 — Tue, 15 Dec 2015 04:03:11 GMT

Hi,

You can use subfield like this,

Table:
LOAD  
Subfield(Field,'||',1) as City,
Subfield(Field,'||',2) as Code


INLINE [
  Field
Brideport||CT
Boston||MA
Albany||NY
];

Out put will be like this:

PFA,

Hope this Helps,

Regards,

HirisH

Re: Substring function in Qlikview ?

avinashelite — Tue, 15 Dec 2015 05:30:23 GMT

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