"IF" code query

[#1]

     IF ([date_built]>=1900 and [date_built] <=1915,'1900 to 1915',
     IF ([date_built]>=1916 and [date_built] <=1930,'1916 to 1930', 
     IF ([date_built]>=1931 and [date_built] <=1945,'1931 to 1945',
     IF ([date_built]>=1946 and [date_built] <=1960,'1946 to 1960',
     IF ([date_built]>=1961 and [date_built] <=1975,'1961 to 1975',
     IF ([date_built]>=1976 and [date_built] <=1990,'1976 to 1990',
     IF ([date_built]>=1991 and [date_built] <=2005,'1991 to 2005',
     IF ([date_built]>=2006,'2006+')))))))) as YearBuilt,

Hi all

I can't seem to get this code to work, but can't figure out why.  It doesn't like some of the commas and one of the brackets.  Can anyone spot an error in the code or suggest an alternative?

Thanks in advance!

Alex

[Gysbert_Wassenaar]

The if statement looks correct. It's possible that date_built is a date instead of a field with numeric year values.

If the field contains year numbers you can also try something like this:

if([date_built]>=2006,'2006+', replace(class([date_built,15), '<= x <','to')) as YearBuilt.

[alexandros17]

Are you sure that date_built has the yyyy format (i.e. is it a year or it is a date?)

let me know

[#4]

Thanks, I'll try that in a sec.

[#5]

Looks like you actually want an intervalmatch. Something like this (I'm assuming the date_built only contains the year, otherwise replacewith dates.) 

YearBuiltRange:

LOAD * INLINE [

    date_built_from, date_built_to, YearBuilt

    1900, 1915

    1916, 1930

    1931, 1945

    1946, 1960

    1961, 1990

    1991, 2005

    2006,

];

IntervalMatch (date_built) LOAD date_built_from, date_built_to Resident YearBuiltRange;

[#6]

We did change the source data in SQL, which was originally coming up with something like "01-01 1901 00:00" with a date and time stamp, to trim to just the year.  From memory though, I think it just came up as YY in SQL but then pulled through OK into Qlikview as the full year, so we thought it was OK.  It sorts fine as a series of separate years in a list table, but just won't band them together.

I've asked the person who worked on the SQL year to have another look at it for me, to see whether it might be that which is affecting it.

Thanks

[#7]

Thanks, I'll give that a go and report back.

[alexandros17]

change your expression to: IF (year([date_built])>=1900 ... (just add year function) obviously to all the conditions ...

let me know

[#9]

Hi, that works - now I'm not sure how to edit the expression in the List Box so that I can use this as an Age Band filter...