topic Re: Getting a string out of a excel cell in QlikView

Getting a string out of a excel cell

Wed, 09 Apr 2014 07:05:23 GMT

hey i'm kind of new to qlikview, so any help would be much appreciated.

What i want to do is get a string out of a cell in a uploaded excel sheet. for example there is feild saying "msg="test message" " the length of the message is different every time....

what i want to do is get the "test message part".. thankyou in advance.. cheers

Re: Getting a string out of a excel cell

MK_QSL — Wed, 09 Apr 2014 07:08:28 GMT

can you provide some more sample alongwith your required result?

Re: Getting a string out of a excel cell

tresB — Wed, 09 Apr 2014 07:09:01 GMT

Have alook at SubField() and TextBetween().

Re: Getting a string out of a excel cell

Anonymous — Wed, 09 Apr 2014 07:10:43 GMT

use

subfield('msg=test','=',2)

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:26:32 GMT

what have is like this

cell1 message

1 msg="new test message"

2 msg="some other message"

what i want is

cell1 message

1 new test message

2 some other message

thank you in advance

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:26:48 GMT

thanks... ill do some research

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:27:41 GMT

thanks... ill do some research.. subField() should work, im tring it out...

Re: Getting a string out of a excel cell

nizamsha — Wed, 09 Apr 2014 07:40:43 GMT

=Replace( SubField(' msg="new test message"','=',-1),'"','')

Re: Getting a string out of a excel cell

nizamsha — Wed, 09 Apr 2014 07:44:19 GMT

TableA:

LOAD * Inline [

MSG

msg="new test message"

msg="some other message"

];

LOAD Replace( SubField(MSG,'=',-1),'"','') as MSG1 Resident TableA;

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:46:48 GMT

new test message and some other message are samples mate... those are not the actual data...

what i basically want to do is get the value after "msg="

thanks anyway

cheers

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:46:58 GMT

new test message and some other message are samples mate... those are not the actual data...

what i basically want to do is get the value after "msg="

thanks anyway

cheers

Re: Getting a string out of a excel cell

Anonymous — Wed, 09 Apr 2014 07:47:12 GMT

Hi,

Use subfield(), read about it in the help and apply the same. Use '=' as delimiter and you are done.

Re: Getting a string out of a excel cell

nizamsha — Wed, 09 Apr 2014 07:48:32 GMT

post sme qvw so we can have a try

if U want to remove msg=mean then subfield() is enough

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 07:57:09 GMT

ok thanks.. ill try it

Re: Getting a string out of a excel cell

MK_QSL — Wed, 09 Apr 2014 08:02:43 GMT

In your script... instead of message, use below...

SubField(Replace(message,'"',''),'=',2) as message

Update :

Method 2

Mid(message,Index(message,'"',1)+1,Index(message,'"',2)-Index(message,'"',1)-1) as message,

Method 3

TextBetween(message,'"','"') as message

Re: Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 08:21:25 GMT

Try this

=SubField('"msg="test message" "','"',3) or

=SubField(YourMessageField,'"',3)

Re: Getting a string out of a excel cell

Wed, 09 Apr 2014 08:40:21 GMT

i took it as an expression rather than on load thankyou all for the support.. much appreciated