topic Re: Search String in QlikView

Search String

Mon, 18 Jul 2011 23:20:17 GMT

Hello All,

How are you?

I have a simple question this time.

I want to search the following strings from the right to find the price. The price value is concatenated with the item id in this manner.

Item_Price:

40637_123ABC_400

40644_1457_2300

40651_12_23005

So all values after the second "_" (underscore) is the price. And all the value before the first underscore "_" is the Julian date

So please suggest how to use the "Right" or Trim function.

Thanks in advance,

Bikash

Re: Search String

gussfish — Mon, 18 Jul 2011 23:32:38 GMT

Hi Bikash,

the answer is that you don't want to use either function! Instead, use

subfield(str,'delimiter',n). This breaks-up the string str into

substrings at every occurence of 'delimiter', and returns the (n)th

substring.

So, in your example, you want subfield(Item_Price,'_',2).

Angus.

Re: Search String

Mon, 18 Jul 2011 23:53:57 GMT

Hi Angus,

Thank you very much.

I got it how the result would look like.

Thanks & Regards,

Bikash

Re: Search String

gussfish — Tue, 19 Jul 2011 00:02:04 GMT

Hi Bikash,

my previous post assumed that your Item_Price would always have the same

number of underscores in it. If this is not the case, and you need to

find the remainder of the string after the last underscore, then use

index(Item_Price, '_', -1 ) to find the position of the last underscore;

then you can use mid() to extract from after that.

Angus.

Re: Search String

Wed, 18 Jan 2012 17:11:45 GMT

Angus

I have the above issue,

Can you please help ?

I have used the following to find each of the ',' , now I would like the text either side

index([Rejection Reasons], ',', -1 )

Can you offer guidence please ?

Thanks,
A

Search String

Wed, 18 Jan 2012 17:21:08 GMT

Meant to say there are multiple ','

Re: Search String

jduenyas — Wed, 18 Jan 2012 18:09:40 GMT

Try this:

Left part = mid([Rejection Reasons],1,index([Rejection Reasons],',',-1)-1)

Or Left Part = left(([Rejection Reasons],index([Rejection Reasons],',',-1)-1)

Right part = mid([Rejection Reasons],index([Rejection Reasons],',',-1)+1,len([Rejection Reasons]))

Re: Search String

Thu, 19 Jan 2012 10:40:18 GMT

Thanks jduenyas