
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Taking the last four characters in a string
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!
- Tags:
- new_to_qlikview
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just write this
=Right(Yearspan,4) as NewFiled
Or
=Right(Yearspan,4)
This will give you below output
2002 |
2009 |
2011 |
2013 |
2014 |
2006 |
2004 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just write this
=Right(Yearspan,4) as NewFiled
Or
=Right(Yearspan,4)
This will give you below output
2002 |
2009 |
2011 |
2013 |
2014 |
2006 |
2004 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi O G,
use right(Yearspan,4).
it Returns you the last 4 parts of your stirng.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
SubField(Yearspan,'-',-1) as LastYear will do the trick.
You can use negative numbers in the SubField function to pick the substrings backwards.
