topic Re: Extracting a substring in QlikView

Extracting a substring

richard_chilvers — Mon, 14 Dec 2015 17:17:47 GMT

I have a string such as:

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

The 8** digits (eg. 803) are fixed identifiers to identify the values they follow.

In this example I want to ignore the '803' and '860' identifiers and preserve the '881' codes eg. D1524, M15329, M1530 etc.

I can use SUBFIELD to unpack the values during a LOAD, but I'm not clear how to get rid of the preceding string first, which I'm not interested in.

Any suggestions?

Thanks

Re: Extracting a substring

sunny_talwar — Mon, 14 Dec 2015 17:20:18 GMT

Not really sure I understand what you are trying to do. Is your data coming from text file like a string? and you just want to extract this alpha-numeric code where it is followed by |,881?

Re: Extracting a substring

Clever_Anjos — Mon, 14 Dec 2015 17:20:18 GMT

Please post your script here

Re: Extracting a substring

richard_chilvers — Mon, 14 Dec 2015 17:23:04 GMT

Sorry it is quite hard to explain. But you are correct. Whenever there is an alphanumeric code between a comma and '|,881' I wish to extract it.

Re: Extracting a substring

sunny_talwar — Mon, 14 Dec 2015 17:32:07 GMT

May be this:

Table:

LOAD Trim(SubField(NewField, '|,', 1)) as FinalField

Where PurgeChar(SubField(NewField, '|,', 2), ',|') = 881;

LOAD SubField(Field, ',|,') as NewField;

LOAD * Inline [

Field

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

] (delimiter is @);

Re: Extracting a substring

JonnyPoole — Mon, 14 Dec 2015 17:35:02 GMT

load

Code,

Identifier

where Identifier='881';

load

subfield(Row,'|',1) as Code,

SubField(Row,'|,',2) as Identifier;

load

SubField(Data & ',',',|,') as Row;

load * inline [

Data

08000355058|,803,|, National|,860,|, D1524|,881,|, M1529|,881,|, M1530|,881,|, M1531|,881,|

] (delimiter is ':') ;

Re: Extracting a substring

sinanozdemir — Mon, 14 Dec 2015 17:43:33 GMT

It seems like you also want to pull the codes from the left side of the first 881. I believe you also don't want to hard code the numbers in Subfield() so you can use IterNo() with it.

Here is the data model:

Hope this helps.

Re: Extracting a substring

richard_chilvers — Tue, 15 Dec 2015 08:32:26 GMT

Thanks - that's neat !

Re: Extracting a substring

richard_chilvers — Tue, 15 Dec 2015 08:33:12 GMT

Thanks everyone for taking the time to look.

All suggestions seem to do the trick !