topic Re: Trimming a value within a formula in QlikView

Trimming a value within a formula

Wed, 09 Mar 2016 21:10:12 GMT

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!

Re: Trimming a value within a formula

sunny_talwar — Wed, 09 Mar 2016 21:36:30 GMT

May be this:

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

Re: Trimming a value within a formula

Colin-Albert — Wed, 09 Mar 2016 21:46:13 GMT

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))