Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Trimming a value within a formula

In the load script, I am trying to subtract

[QT_EFFECTIVE_DATE] - [HO_CONSTRUCTION_YEAR] AS AGE

But the data types are a bit different. For [QT_EFFECTIVE_DATE], the data looks like 20150124 and [HO_CONSTRUCTION_YEAR] is just the 4 digit year, like 2008.  So I need to trim the right 4 digits of [QT_EFFECTIVE_DATE] to just extract the year so the math will work. What would the syntax look like?  I tried the above but got an "invalid expression" error. 

Will trimming it work? Or will I need to also convert the data types as well? If so, what does that syntax look like?

Thanks!

2 Replies

Re: Trimming a value within a formula

May be this:

Num#(Left([QT_EFFECTIVE_DATE], 4), '##') - [HO_CONSTRUCTION_YEAR] AS AGE

Re: Trimming a value within a formula

QlikView has an age function.

You will need to load your QT_EFFECTIVE_DATE as a date field.

AGE(Date#(QT_EFFECTIVE_DATE, 'YYYYMMDD'), makedate(HO_CONSTRUCTION_YEAR))