topic Re: How to get particular text from the full text using substring or other function? in QlikView

How to get particular text from the full text using substring or other function?

udaya_kumar — Sun, 15 Jul 2012 06:38:09 GMT

Hi,

I have a field by name A_name, which has data like

market name1 product name1 TRx Volume,

market name2 product name2 TRx Share,

market name3 product name3 product NRx Volume,

market name1 product name2 product name1 NRx Share

Now i want to take only TRx Volume, TRx Share, NRx Volume, NRx Share from those data separately.

how we can get that text from the data?

Re: How to get particular text from the full text using substring or other function?

deepakk — Sun, 15 Jul 2012 07:04:38 GMT

hi,

Use the subfield function as below

subfield(A-name,' ',4)

Deepak

Re: How to get particular text from the full text using substring or other function?

udaya_kumar — Sun, 15 Jul 2012 07:13:54 GMT

Hi Deepak,

Thanks for reply,

I treid this, but the problem is, i want to get TRx Volume.

I tried this, for some data, it has many words like

market name1 product name1 market name2 product name3 TRx Volume.

in this case, it is giving me 'market' as the result for ur expression.

Re: How to get particular text from the full text using substring or other function?

Jason_Michaelides — Sun, 15 Jul 2012 08:29:41 GMT

Just change the third parameter:

SubField(A-name,' ',6) - this will get the 6th bit of the string when broken up by spaces.

Bear in mind that NRx Share values and TRx Volume values will all be mixed in together by this method. Maybe add another field so you'll have:

Data:

LOAD

SubField(A-name,' ',5) AS TxType //this will give TRx or NRx

,SubField(A-name,' ',6) AS TxValue //this will give the Share or Volume number

etc

FROM....;

Hope this helps,

Jason

Re: How to get particular text from the full text using substring or other function?

udaya_kumar — Sun, 15 Jul 2012 08:57:41 GMT

Hi Jason,

thanks for reply,

My problem is that, the data is not having fixed no. of words.

it differs for every row like

market name1 product name1 TRx Volume

market name2 product name2 market name2 product name1 TRx Share

.....

In this case, the subfield(field,'5') will give Volume but in second data, it will give name2.

so that is the problem i cannot use subfield.

Is there any function to match data like Start from TRx or NRx and take till end of that like Volume or Share?

Re: How to get particular text from the full text using substring or other function?

Jason_Michaelides — Sun, 15 Jul 2012 09:15:35 GMT

Ah, I see. In that case, you could use Mid() with FindOneOf() or Indez(), or you could still use SubField() but with a different delimiter:

Data:

LOAD

SubField(A-name,'TRx ',1) AS TRxVolume

,SubField(A-name,'NRx ',1) AS NRxShare

etc

FROM....;

Hope this helps,

Jason

Re: How to get particular text from the full text using substring or other function?

udaya_kumar — Sun, 15 Jul 2012 10:14:26 GMT

Thank u very much Jason,

I used subfield function to get it.

=subfield(A_NAME,' ',-2) & ' ' & SubField(A_NAME,' ',-1)