topic Get part of a string in QlikView

Get part of a string

Tue, 24 Jan 2012 13:11:39 GMT

Hi Everyone,

I have two fields that mean the same from 2 different tables, but are not the same in characters.

The first one is like ODI-1, the second one is like [#ODI-1].

I have tried mid(field, 3, 5) and that works for now. But what do to when we reach [#ODI-10] until [#ODI-99] or higher?

Or is there maybe another way to connect these fields because they mean the same?

Get part of a string

SunilChauhan — Tue, 24 Jan 2012 13:14:00 GMT

use

subfield(fieldname,'#',2)

Get part of a string

swuehl — Tue, 24 Jan 2012 13:18:35 GMT

You could also look into purgechar if the difference in table 1 and table 2 are characters that table 2 has, but table1 doesn't use.

purgechar('[#ODI-1]','[]#') as LINKFIELD,

Regards,

Stefan

Get part of a string

Tue, 24 Jan 2012 13:19:55 GMT

Sunil,

That one is close but it gets me ODI-1].

Get part of a string

CELAMBARASAN — Tue, 24 Jan 2012 13:26:05 GMT

Hi,

Try this

keepchar ( '#[ODI-10]','ODI-0123456789' )

Celambarasan

Get part of a string

Tue, 24 Jan 2012 13:28:47 GMT

Hi Celambarasan,

What would I do with that?

ODI-1 was just an example. It can be any combination of 3 letters with a - and a number up to 999 (in theory)

Re: Get part of a string

swuehl — Tue, 24 Jan 2012 13:33:50 GMT

If your second table field just shows some defined additional characters, use purgechar

purgechar( FIELDNAME, '[]#' ) as FIELDNAME,

if your additional characters are undefined, but your characters you want to keep are known and a smaller set, you could use keepchar (just add all letters a to z to above literal, second parameter to keepchar). In both cases you would need to know about the possible character sets in both fields.

Get part of a string

CELAMBARASAN — Tue, 24 Jan 2012 13:38:10 GMT

Hi,

Then you ve to use either

purgechar('[#ODI-1]','[]#')

keepchar ( '#[ODI-10]','ABCDEFGHIJKLMNOPQRSTUVWXYZ-0123456789' )

replace(replace('[#ODI-01]' , '[#' , '') , ']' , '')

Celambarasan

Get part of a string

SunilChauhan — Tue, 24 Jan 2012 13:39:42 GMT

may be this u want

subfield(subfield(fieldname,'#',2) ,'-',1)

Get part of a string

Tue, 24 Jan 2012 13:39:46 GMT

Thanks swuehl,

The second table can contain any kind of text, it's a remark field. But when it has the same meaning as the first table, the field only contains a value like [#abc-1] or [#def-2]. So, the first two characters are always [# but the rest can differ from xxx-1 until xxx-9999. And that is what I want to see.

Get part of a string

SunilChauhan — Tue, 24 Jan 2012 13:41:01 GMT

may be this

subfield(subfield(fieldname,'#',2) ,'-',1)

Get part of a string

Tue, 24 Jan 2012 13:57:12 GMT

And how about addin [#] to the first table? Don't know how that works. But the first table field only contains abc-1 and so on.

Get part of a string

Tue, 24 Jan 2012 13:57:56 GMT

Hi Sunil,

That doesn't work. Would be nice if it would though.

Re: Get part of a string

swuehl — Tue, 24 Jan 2012 13:59:04 GMT

LOAD

...

'[#' & FIELD & ']' as FIELD,

...

from Table1;

edit: Have you tried the purgechar() ?

Re: Get part of a string

SunilChauhan — Tue, 24 Jan 2012 13:59:16 GMT

for first table use can use

fieldname as somename

and in second Table

use this

subfield(fieldname,'#',2) as somename

hope this helps

Get part of a string

Tue, 24 Jan 2012 14:35:33 GMT

Thanks swuehl, that worked. Now I have the same fields. Thanks all!