Discussion Board for collaboration related to Creating Analytics for QlikView.
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?
Go to Solution.
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:
SubField(A-name,'TRx ',1) AS TRxVolume
,SubField(A-name,'NRx ',1) AS NRxShare
Hope this helps,
Use the subfield function as below
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.
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:
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
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?
Thank u very much Jason,
I used subfield function to get it.
=subfield(A_NAME,' ',-2) & ' ' & SubField(A_NAME,' ',-1)