topic Re: Age range with If in QlikView

Age range with If

pauledrich — Thu, 10 Oct 2013 16:39:38 GMT

A bit of a novice with the script here and in need of a bit of support...

I have the following Load which isn't working due to the added "if".

I would like to take the customers DOB and apply those into the bands shown below so as to compare age ranges to products purchased; Any help would be appreciated.

CustomerProfile:

Load

CustDateOfBirth,
Date (Floor(CustDateOfBirth)), 'DD/MM/YYYY' as DobDate,
Year(Today(1))-Year(Date(CustDateOfBirth)) AS CustAge,
CustFirstTransaction,
CustID;
Left Join
Load *,
If (CustAge <=20, AgeUnder20,
If (CustAge >20<=30, Age20_30,
If (CustAge >30<=40, Age30_40,
If(CustAge >40<=50, Age40_50,
If (CustAge >50<=60, Age50_60,
If (CustAge >60<=70, Age60_70, 'Over70')))))) as AgeBand;

SQL SELECT CustDateOfBirth,
CustFirstTransaction,
CustID
FROM **************;

Thanks

Re: Age range with If

swuehl — Thu, 10 Oct 2013 16:47:02 GMT

There are more efficient ways to do this:

creating an age group from a date field

Re: Re: Age range with If

pauledrich — Thu, 10 Oct 2013 17:57:12 GMT

Thanks for the reply.... I have amended from the helpfile to what is below - during debug QV isn't recognising the "CustAge" field I created from the "CustDateOfBirth" Field, I have tried "[CustAge]", 'CustAge' - clearly im making a basic error somewhere?

Thanks again.....

CustomerProfile:

Load

CustDateOfBirth,

Date (Floor(CustDateOfBirth)), 'DD/MM/YYYY' as DobDate,

Year(Today(1))-Year(Date(CustDateOfBirth)) AS CustAge,

CustFirstTransaction,

CustID;

Left Join

Load *,

If (CustAge <=20, 'AgeUnder20',

If (CustAge>20 and CustAge <=30, 'Age20_30',

If (CustAge >30 and CustAge <=40, 'Age30_40',

If (CustAge >40 and CustAge <=50, 'Age40_50',

If (CustAge >50 and CustAge <=60, 'Age50_60',

If (CustAge >60 and CustAge <=70, 'Age60_70', 'Over70')))))) as AgeBand;

SQL SELECT CustDateOfBirth,

CustFirstTransaction,

CustID

FROM *******;

Re: Re: Re: Age range with If

swuehl — Thu, 10 Oct 2013 18:05:25 GMT

Ok, if you want to go with the if() statements, you need to correct the order of your preceding loads (you need to read the load statements from the bottom up, like a pipe), and don't think you should use the left join here:

CustomerProfile:

Load *,

If (CustAge <=20, 'AgeUnder20',

If (CustAge>20 and CustAge <=30, 'Age20_30',

If (CustAge >30 and CustAge <=40, 'Age30_40',

If (CustAge >40 and CustAge <=50, 'Age40_50',

If (CustAge >50 and CustAge <=60, 'Age50_60',

If (CustAge >60 and CustAge <=70, 'Age60_70', 'Over70')))))) as AgeBand;

Load

CustDateOfBirth,

Date (Floor(CustDateOfBirth), 'DD/MM/YYYY') as DobDate,

Year(Today(1))-Year(Date(CustDateOfBirth)) AS CustAge, // I suggest using age() function instead, which return the day accurate age

CustFirstTransaction,

CustID;

SQL SELECT CustDateOfBirth,

CustFirstTransaction,

CustID

FROM *******;

Re: Re: Age range with If

pauledrich — Thu, 10 Oct 2013 18:14:03 GMT

Thanks for the quick reply - I went with the if as a start point to establish whether to move fwd with the information although I do take the points covered in the answers.

Very much appreciate the help.

Thanks again.