topic Re: Taking the last four characters in a string in QlikView

Taking the last four characters in a string

Thu, 24 Apr 2014 14:37:40 GMT

Hi!

I have an excel sheet where one of the fields is 'Yearspan'. Within this 'Yearspan' field, there are some cells which include data in the format: 'XXXX-XXXX' and others which contain the format 'XXXX', where 'XXXX' is a year, i.e. '2001-2002', or '2002'. An example of this would be...

'Yearspan

2001-2002

2009

2010-2011

2013

2014

2005-2006

2003-2004'

and so on...

I want to create a column that only takes the second year if the format is 'XXXX-XXXX', so if '2001-2002', only take '2002'. If the field is 'XXXX', just to take that year.

The end result needs to be a column which only includes dates in the 'XXXX' format, i.e. just one year.

Is this possible? Thank you!

Re: Taking the last four characters in a string

its_anandrjs — Thu, 24 Apr 2014 14:44:09 GMT

Just write this

=Right(Yearspan,4) as NewFiled

=Right(Yearspan,4)

This will give you below output

2002

2009

2011

2013

2014

2006

2004

Re: Taking the last four characters in a string

lukaspuschner — Thu, 24 Apr 2014 14:44:29 GMT

Hi O G,

use right(Yearspan,4).

it Returns you the last 4 parts of your stirng.

Re: Taking the last four characters in a string

whiteline — Thu, 24 Apr 2014 14:46:57 GMT

Hi.

You can use right() function in load script to get the four digits and num#() function to convert the string into number.

num#(right(Yearspan, 4),'0') as Year

Read the QV help about string functions. There are many useful things.

Re: Taking the last four characters in a string

msvanfeldt — Thu, 24 Apr 2014 14:56:25 GMT

SubField(Yearspan,'-',-1) as LastYear will do the trick.

You can use negative numbers in the SubField function to pick the substrings backwards.