topic Re: text functions in QlikView

text functions

ilanbaruch — Mon, 07 Dec 2015 08:24:32 GMT

hi,

I have a field in a table that holds information ..

output example:

<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>

I need only the ResolvedIP Value (111.222.44.333)

any idea how can I exclude it and create an IP field?

thank you

Ilan

Re: text functions

marcus_sommer — Mon, 07 Dec 2015 08:29:25 GMT

Try:

= textbetween('<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>', 'ResolvedIP=', '>')

- Marcus

Re: text functions

HirisH_V7 — Mon, 07 Dec 2015 09:00:30 GMT

Hi ,

You can Split the Field like this below,

T:
LOAD
Mid([IP field],21,27) as NewIP,
'<' &TextBetween([IP field],'><','><') &'>'as NewIP2,
* INLINE [
IP field
<Account=jack johns><ResolvedIP=111.222.44.333><ResolvedCountry=AA>
];

By using Mid Function and TextBetween Function,

Output:

Hope this Helps,

PFA,

Regards,

HirisH

Re: text functions

Mon, 07 Dec 2015 09:01:36 GMT

Hi iLan,

You can try the textbetween as mentioned by Marcus, there can be many ways of achieving this using string functions in qlikview.

sol 1:

= PurgeChar(SubField('<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>',',',2),'<ResolvedIP=<>,');

sol 2 :

= PurgeChar(mid('<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>', index('<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>','P=')+2,15),',<>')

Hope it helps.

Re: text functions

awhitfield — Mon, 07 Dec 2015 09:49:46 GMT

Hi ilan,

can you supply an example data file please?

Andy

Re: text functions

ilanbaruch — Mon, 07 Dec 2015 10:20:39 GMT

thank you it helps,

if I want it to work on all the table data, some name values other then 21,27 ?

for example :

<Account=cristiano johns><ResolvedIP=111.222.44.333><ResolvedCountry=AA>

Re: text functions

avinashelite — Mon, 07 Dec 2015 10:22:30 GMT

please share the sample app with the data

Re: text functions

HirisH_V7 — Mon, 07 Dec 2015 10:29:31 GMT

Hi ilan ,

Please check updated post.

-Hirish

Re: text functions

marcus_sommer — Mon, 07 Dec 2015 10:31:33 GMT

A pure solution with mid() couldn't stable work - the content of the string will be probably different so that you need further search-functions like index() - but then is textbetween() easier.

Another possibilty could be to split the complete string with subfield() like:

Load subfield(Field, '=', 1) as Attribut, subfield(Field, '=', 2) as Value;

Load purgechar(subfield(Field, ','), '<>') as Field;

Load * Inline [

Field

<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>

];

- Marcus

Re: text functions

Kushal_Chawda — Mon, 07 Dec 2015 11:26:48 GMT

try this

Data:

LOAD *,subfield(purgechar(SubField(mid(Input,Index(Input,'ResolvedIP')),',',1),'>'),'=',2) as Output Inline [

Input

"<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>"

"<Account=jack johns>,<ResolvedCountry=AA>,<ResolvedIP=12.22.33.44>" ];

Re: text functions

effinty2112 — Mon, 07 Dec 2015 12:08:19 GMT

Yet another solution that cares not the order of the terms in the angle brackets:

=SubField(

Right(TextToBeScanned,

Index(TextToBeScanned, 'ResolvedIP=') + len('<ResolvedIP=')+1)

, '>',1)

Re: text functions

ilanbaruch — Mon, 07 Dec 2015 13:01:10 GMT

hi,

thank you for your help, can I set as input the original field?

Re: text functions

ilanbaruch — Mon, 07 Dec 2015 13:01:42 GMT

hi,

thank you for your help, can I set as 'TextToBeScanned' the original field?

Re: text functions

effinty2112 — Mon, 07 Dec 2015 13:07:30 GMT

Hi Ilan,

Yes replace 'TextToBeScanned' with your field. Sorry if I was not very clear.

Cheers

Andrew

Re: text functions

Kushal_Chawda — Mon, 07 Dec 2015 13:11:39 GMT

Yes, You can replace Input with your actual field name

Re: text functions

ilanbaruch — Tue, 08 Dec 2015 08:43:28 GMT

hi and thank you,

in sol1 can I use the field instead of the string (<Account=jack johns>,<ResolvedIP=111.222.44.333>,<ResolvedCountry=AA>')?

like this:

PurgeChar(SubField(INFO,',',2),'<ResolvedIP=<>,');