topic Re: discard in QlikView

discard

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a field by name Description within whic the data is like

nitiative I12878: Business Planet MVP 1 Get a Plan

nitiative I12849: Business Planet MVP 2 Get a Plan

nitiative I12868: Business Planet MVP 1 Get a Plan

...

I am using keepchar function to take only "I12868"

Keepchar(Description,0123456789') as desc

But the thng is i am getting the extra number also which is present in this line lik "1"

but i want only the number followed by I that is "I12849" id's ,

how do we eliminate the other numbers ?

regards

Re: discard

Anil_Babu_Samineni — Fri, 26 Aug 2016 10:54:31 GMT

In you data String also there

Keepchar(Description,'I0123456789') as desc

Here, I is caps lock

Re: discard

Anonymous — Fri, 26 Aug 2016 11:00:19 GMT

try this

if you would like to have only 'I12849' ,'I2878'........etc.,

SubField(Description, ' : ', 1)

Re: discard

smilingjohn — Fri, 26 Aug 2016 11:02:43 GMT

Thanks Anil,

This i have tried , but the thing is that along with the ID its taking all the numbers present in that line

for example if the data is like

Descriptio:

"Initiative I12878: Business Planet MVP 1 Get a Plan"

Keepchar(Description,'I0123456789') as desc

then the expected output should be like this : I12878

Actaul : I128781 look it is taking the 1 also

I hope i am clear in explaining

Re: discard

Colin-Albert — Fri, 26 Aug 2016 11:04:03 GMT

Try using TextBetween()

TextBetween(Description, '|', ':') as desc

Re: discard

Anil_Babu_Samineni — Fri, 26 Aug 2016 11:06:51 GMT

My Mistake, Check This

Re: discard

Colin-Albert — Fri, 26 Aug 2016 11:08:45 GMT

If you wish to retain the pipe character the use

TextBetween(Description, ' ', ':') as desc

'|' & TextBetween(Description, '|', ':') as desc

Re: discard

smilingjohn — Fri, 26 Aug 2016 11:13:47 GMT

Hi Alluraiah,

I tried using your your script

SubField(Description, ' : ', 1) as id

But it did not work the out put is same as the data , it did not make any change

Re: discard

Anonymous — Fri, 26 Aug 2016 11:26:16 GMT

You can try this as well

SUBFIELD(SubField(Description, ':', 1),' ',2) as ID

anyway you got the correct answer from Colin ...