topic Re: "IF" code query in QlikView

"IF" code query

Thu, 11 Jul 2013 14:38:57 GMT


     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

Re: "IF" code query

Gysbert_Wassenaar — Thu, 11 Jul 2013 14:50:35 GMT

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.

Re: "IF" code query

alexandros17 — Thu, 11 Jul 2013 14:51:50 GMT

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

let me know

Re: "IF" code query

Thu, 11 Jul 2013 15:02:22 GMT

Thanks, I'll try that in a sec.

Re: "IF" code query

Thu, 11 Jul 2013 15:06:36 GMT

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;

Re: "IF" code query

Thu, 11 Jul 2013 15:10:29 GMT

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

Re: "IF" code query

Thu, 11 Jul 2013 15:13:52 GMT

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

Re: "IF" code query

alexandros17 — Thu, 11 Jul 2013 15:14:34 GMT

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

let me know

Re: "IF" code query

Thu, 11 Jul 2013 15:29:23 GMT

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