Age range with If

[pauledrich]

Hi

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

P

[swuehl]

There are more efficient ways to do this:

creating an age group from a date field

[pauledrich]

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 *******;

P

[swuehl · Accepted solution]

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 *******;

[pauledrich]

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.

P