topic If Then Statement - Loading Date Ages in QlikView

If Then Statement - Loading Date Ages

jmonroe918 — Sat, 25 Jan 2014 23:16:45 GMT

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

Re: If Then Statement - Loading Date Ages

phaneendra_kunc — Sat, 25 Jan 2014 23:25:57 GMT

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

Re: If Then Statement - Loading Date Ages

brindlogcool — Sun, 26 Jan 2014 00:20:28 GMT

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.

Re: If Then Statement - Loading Date Ages

Sokkorn — Mon, 27 Jan 2014 04:24:15 GMT

Hi Jeff,