If Then Statement - Loading Date Ages

[jmonroe918]

In my load statements I want to do the following:

Calculate the age of an open record (Today - Create Date)  as "Age (Days)".

I then want to use Age Days to categorize in a field called "Days Open". I am using an if/then statement that will enter into the field "<30" for records with ages less than 30, "31-60" for those that fit that criteria, "61-90" for the next and the all others ">90" etc.

The Age (Days) seems to calculate all right, but I get an error from my nested if/then statments ([Age (Days)] not found).

Can I not use [Age (Days)] in the statement? Or do I have to paste the formula at each instance?  See below.

Jeff

LOAD

[Create Date],
[Close Date],
(Today())-[Create Date] as [Age (Days)],

     IF([Age (Days)]>=0 and [Age (Days)]<=30, '<=30',
     IF([Age (Days)]>30 and [Age (Days)]<61, '31-60',
     IF([Age (Days)]>60 and [Age (Days)]<91, '61-90',
     IF([Age (Days)]>90 and [Age (Days)]<180, '91-180',
     IF([Age (Days)]>180 and [Age (Days)]<365, '181-365',
     '>365'
     )))))
     as [Days Open]

FROM

etc....

[phaneendra_kunc]

you are trying to use the field that is not yet available...

try like this..

Load [Create Date],

[Close Date],

[Age (Days)],

IF([Age (Days)]>=0 and [Age (Days)]<=30, '<=30',

     IF([Age (Days)]>30 and [Age (Days)]<61, '31-60',

     IF([Age (Days)]>60 and [Age (Days)]<91, '61-90',

     IF([Age (Days)]>90 and [Age (Days)]<180, '91-180',

     IF([Age (Days)]>180 and [Age (Days)]<365, '181-365',

     '>365'

     )))))

     as [Days Open]

     ;

LOAD

[Create Date],

[Close Date],

(Today())-[Create Date] as [Age (Days)] 

FROM

[brindlogcool]

You can use the class function.. From Ref manual...

class(expression, interval [ , label [ , offset ]])

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

[Sokkorn]

Hi Jeff,

Similar to previous post

LOAD*,

     IF([Age (Days)]>=0 and [Age (Days)]<=30, '<=30',
     IF([Age (Days)]>30 and [Age (Days)]<61, '31-60',
     IF([Age (Days)]>60 and [Age (Days)]<91, '61-90',
     IF([Age (Days)]>90 and [Age (Days)]<180, '91-180',
     IF([Age (Days)]>180 and [Age (Days)]<365, '181-365',
     '>365'
     ))))) as [Days Open];

LOAD

[Create Date],
[Close Date],
Floor(Today()-[Create Date]) as [Age (Days)];

FROM

etc....