text functions

[ilanbaruch]

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

[marcus_sommer]

Try:

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

- Marcus

[HirisH_V7]

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

[#4]

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.

VC

[awhitfield]

Hi ilan,

can you supply an example data file please?

Andy

[ilanbaruch]

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>

[avinashelite]

please share the sample app with the data

[HirisH_V7]

Hi ilan ,

Please check updated post.

-Hirish

[marcus_sommer]

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

[Kushal_Chawda]

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>"  ];