topic Re: String right function not working properly in QlikView

String right function not working properly

karthikeyan1504 — Tue, 19 Apr 2016 09:39:15 GMT

Hi all,

I have a single field value as mentioned below,

KSN_PXRU4471 001

From above value, I need the characters after underscore 'PXRU4471 001'.

When I tried with the formula of '=Right(ITEM_KEY,Index(ITEM_KEY,'_'))', I get an output as '0001'.

Could you please let me know whether I am missing anything?

Thanks & Regards,

Karthikeyan.

Re: String right function not working properly

sunny_talwar — Tue, 19 Apr 2016 09:40:21 GMT

Have you tried SubField?

=SubField('KSN_PXRU4471 001', '_', 2)

=SubField('KSN_PXRU4471 001', '_', -1)

Re: String right function not working properly

sunny_talwar — Tue, 19 Apr 2016 09:41:53 GMT

If you want to use Right, you can try this:

=Right('KSN_PXRU4471 001', Len('KSN_PXRU4471 001') - Index('KSN_PXRU4471 001', '_'))

Re: String right function not working properly

arulsettu — Tue, 19 Apr 2016 09:44:17 GMT

may be this

Right('KSN_PXRU4471 001',15)

Re: String right function not working properly

Tue, 19 Apr 2016 09:46:35 GMT

It is better to use Subfield function for the data extraction when the data seperated by any delimiter.

Re: String right function not working properly

nico_ilog — Tue, 19 Apr 2016 09:53:28 GMT

Try this,

Right(YourFieldName, len(YourFieldName)-Index(YourFieldName, '_') as YourField

Nico

Re: String right function not working properly

tamilarasu — Tue, 19 Apr 2016 09:54:18 GMT

There are many ways to get the result. Subfield looks simple compared to other methods

=SubField(' KSN_PXRU4471 001', '_', 2)

=Right('KSN_PXRU4471 001', Len('KSN_PXRU4471 001') - FindOneOf('KSN_PXRU4471 001', '_'))

=Mid('KSN_PXRU4471 001', Index('KSN_PXRU4471 001', '_')+1)

Re: String right function not working properly

swuehl — Tue, 19 Apr 2016 10:03:19 GMT

You can get what you want with an expression like above.

But why do you think the right() function is not working properly?

"right(text(ITEM_KEY),4)', I get an output as '0001'."

0001 are the four rightmost characters in ITEM_KEY. So I can't see any issue with Right() function here.

Or how do you expect the Right() function to work?

Re: String right function not working properly

rubenmarin — Tue, 19 Apr 2016 10:04:41 GMT

Hi Karthikeyan

Right() will return the last X characters of a string, so '=right(text(ITEM_KEY),4) can be translated as "give me the 4 last characters of each ITEM_KEY.

For your intentions it seems that Mid() is what you were looking for, something like: =Mid(Text(ITEM_KEY), 5), wich can be translated as "give me all the string from the 5th characther (removing the first 4 characters)

The options proposed by other users can also work.

Re: String right function not working properly

karthikeyan1504 — Tue, 19 Apr 2016 10:33:59 GMT

I am sorry. I have edited my question now.

The function I have used is =Right(ITEM_KEY,Index(ITEM_KEY,'_'))

Re: String right function not working properly

sunny_talwar — Tue, 19 Apr 2016 11:09:29 GMT

I think the issue is that the Index function gives you the location of the first instance of '_' which is at number 4 spot and that is why you see 4 character getting pulled from the right. In order to get to '_' from the right, you need to do Len(Field) - Index(Field, '_') -> Which will get you the number of characters to be pulled from right before you hit '_'

Re: String right function not working properly

swuehl — Tue, 19 Apr 2016 11:13:57 GMT

Or just use

Mid( Field, Index(Field, '_')+1)

as suggested above.

(Or subfield(), but this only works in your case if the separator '_' is only occuring once).

edit:

My point is, choose the most appropriate solution for your setting. To be able to do this, learn more about string functions:

String functions ‒ QlikView

Re: String right function not working properly

cbaqir — Thu, 21 Sep 2017 19:37:00 GMT

stalwar1‌

Sunny,

Is there some way I could use this type of syntax in the load script with the stripHMTL module?

TextBetween( stripHTML(DESCRIPTION) , 'Analytics:' , 'XXXXXX ) as ANALYTICS,

Analytics gets truncated in the field value so it's always something different. My thought was to use the Right() to identify the last few characters of the field as the ending point of the value for the new ANALYTICS field. Make sense?