topic Grouping ages into bands in QlikView

Grouping ages into bands

Thu, 07 Aug 2014 09:25:59 GMT

New to Qlikview

So I have a huge set of data with information on different people. Ages range from 17 to 93. Is there a way I can simply group these ages into bands of 5. So instead of having age as 17,18,19...,93 it would be 17-21, 22-26,....

(A bit of context, I want to group these ages and work out the average Insurance premium charged in each age group. So instead of having an average for each age seperately it would have an average for each age group.)

Cheers

(Sorry if posted in wrong forum)

Re: Grouping ages into bands

marcus_malinow — Thu, 07 Aug 2014 09:29:21 GMT

Kristian,

a couple of ways to achieve this spring to mind.

Either, set up a table with the ranges and use an IntervalMatch in your load

Or if your groups are the same length (e.g. all are 5 years), you can use a Class in your charts.

Marcus

Re: Grouping ages into bands

deepakqlikview_123 — Thu, 07 Aug 2014 09:30:06 GMT

Hi,

You can use class function for this

Thanks

Re: Grouping ages into bands

kaushiknsolanki — Thu, 07 Aug 2014 09:37:30 GMT

HI,

Have a look at the example posted on this post.

Class function | Qlik Community

Regards,

Kaushik Solanki

Re: Grouping ages into bands

yduval75 — Thu, 07 Aug 2014 09:48:31 GMT

I think this link will help you.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/18/dual-exists-useful-functions

Re: Grouping ages into bands

Thu, 07 Aug 2014 09:50:42 GMT

Cheers for the help guys. Class worked just fine.

Much appreciated.

Re: Grouping ages into bands

MarcoWedel — Thu, 07 Aug 2014 10:41:07 GMT

Custom Filter(unusual range)

Maybe also helpful if there would be unequal sized groups.

Regards

Marco

Re: Grouping ages into bands

Thu, 07 Aug 2014 10:49:34 GMT

Im now on something new and require grouping in different sizes. So now a list called 'Clicks' which is 0,1,2,3,4...15 I now need as 0,1,2,3-5,6-10,11+

I know you mentioned IntervalMatch but I literally have no idea how to use that.

Thanks again

Re: Grouping ages into bands

marcus_malinow — Thu, 07 Aug 2014 11:20:59 GMT

Hi Kristian,

Marco's link above gives an example of IntervalMatch which may be useful.

Otherwise you could do something like this:

Data:

LOAD MatchedValue

etc, etc;

Intervals

LOAD * INLINE [

RangeName, RangeMin, RangeMax

0, 0, 0

1, 1, 1

2, 2, 2

3 - 5, 3, 5

etc, etc...

];

LEFT JOIN (Data)

IntervalMatch (MatchedValue)

LOAD RangeMin, RangeMax

RESIDENT Intervals;

LEFT JOIN (Data)

LOAD

RangeMin, RangeMax, RangeName

RESIDENT Intervals;

DROP TABLE Intervals;

Re: Grouping ages into bands

Thu, 07 Aug 2014 11:30:32 GMT

Sorry for this, like I said I'm completely new to qlikview, but what should I put in the first 'etc. etc.'?

Re: Grouping ages into bands

marcus_malinow — Thu, 07 Aug 2014 11:38:50 GMT

That part is just your existing data load.

Just change MatchedValue for whatever the name of the field is that you're trying to match, and also change it in the rest of the script.

If you have issues, just post your existing load script.

Re: Grouping ages into bands

Thu, 07 Aug 2014 12:34:32 GMT

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

LOAD EnquiryKey,

EnquiryDate,

EnquiryDateTime,

PolicyStartDate,

DaysToInception,

QuoteHour,

CarRegistrationHash,

ABICode,

PostcodeCompress,

CarValue,

ManufacturedYear,

VehicleAge,

EngineCC,

NumberOfSeats,

FuelType,

TrackerFitted,

BodyShape,

Alarm,

Import,

RightHandDrive,

VehicleOwner,

RegisteredKeeper,

OvernightLocation,

DaytimeLocation,

VehicleUsage,

AnnualPersonalMileage,

AnnualBusinessMileage,

AnnualMileage,

TotalCarsInHousehold,

CarOwned,

FirstStartedDrivingCar,

MonthsCarOwned,

ProtectedNoClaimsDiscount,

RequestedVoluntaryExcess,

PreferredPaymentFrequency,

CoverType,

DeviceTypeQuotedOn,

DeviceOSQuotedOn,

DeviceBrowserQuotedOn,

HomeOwner,

NumberOfChildrenUnder16,

NoClaimDiscountYears,

PreviouslyDeclinedInsurance,

EmailAddressDomain,

VehicleMake,

VehicleModel,

VehicleDoors,

VehicleFuel,

VehicleGear,

ABI_Car_Group,

Vehicle_Type,

GLASS_RISK,

VehicleGroup_FT,

VehicleGroup_PD,

Vehicle_Model_Group_Y,

VehicleRisk_Y,

VehicleRisk_O,

VehicleBodyType,

EXO_ABI_BHP,

EXO_ABI_HEIGHTMM,

EXO_ABI_LENGTHMM,

EXO_ABI_WEIGHTKG,

PowerToWeight,

Rating_District_AD,

Rating_District_PD,

Rating_District_PI,

Rating_District_FT,

Rating_District_WS,

HH_Density,

Geo_Delphi,

Risk_Area,

TotalNumberOfQuotes,

MarketPriceTop5Annual,

MarketPriceTop5Monthly,

MarketPriceNext5Annual,

MarketPriceNext5Monthly,

DirectInsPriceTop5Annual,

DirectInsPriceTop5Monthly,

BrokerPriceTop5Annual,

BrokerPriceTop5Monthly,

BigBrandsPriceTop5Annual,

BigBrandsPriceTop5Monthly,

MediumBrandsTop5Annual,

MediumBrandsTop5Monthly,

LittleBrandsPriceTop5Annual,

LittleBrandsPriceTop5Monthly,

TelematicsPriceTop5Annual,

TelematicsPriceTop5Monthly,

CustomerClickedThrough,

CountClickThroughWholeOfMarket,

CountClickThroughDirectInsurers,

CountClickThroughBrokers,

CountClickThroughBigBrands,

CountClickThroughMediumBrands,

CountClickThroughLittleBrands,

CountClickThroughTelematics,

UsedGoToSite,

UsedPreferToPhone,

SecondsBetweenQuoteAndFirstClic,

AncillaryInterestWindscreen,

AncillaryInterestCourtesyCar,

AncillaryInterestBreakdown,

AncillaryInterestPersonalAccide,

AncillaryInterestLegal,

MoreThanAnnualPrem,

MoreThanMonthlyPrem,

MoreThanExcess,

MoreThanUsedGotoSite,

MoreThanUsedPreferToPhone,

MoreThanPolicyNumber,

MoreThanCreditScore,

MoreThanCreditScoreSource,

MoreThanSale,

MoreThanLeadIndicator,

MoreThanRank,

MoreThanElasticityScore,

MoreThanStrategyNumber,

MthanMainEqualToRated,

EChoiceAnnualPrem,

EChoiceMonthlyPrem,

EChoiceExcess,

EChoiceUsedGotoSite,

EChoiceUsedPreferToPhone,

EChoicePolicyNumber,

EChoiceCreditScore,

EChoiceCreditScoreSource,

EChoiceSale,

EChoiceLeadIndicator,

EChoiceRank,

EChoiceElasticityScore,

EChoiceStrategyNumber,

EchoiceMainEqualToRated,

AverageTotalExcessTop5,

AncillaryCountWindscreenTop5,

AncillaryCountCourtesyCarTop5,

AncillaryCountBreakdownTop5,

AncillaryCountPersonalAccidentT,

AncillaryCountLegalTop5,

ExclusiveDealCountTop5,

Sale,

PeergroupSize,

PeergroupDirectOrBroker,

PeergroupTelematics,

RankOfFirstClick,

D1_RelationshipToPolicyHolder,

D1_Gender,

D1_DateOfBirth,

D1_MaritalStatus,

D1_MainOccupation,

D1_MainBusinessType,

D1_MainEmploymentStatus,

D1_AdditionalOccupation,

D1_AdditionalBusinessType,

D1_UKResidentSinceBirth,

D1_UKResidentSince,

D1_LicenceType,

D1_LicenceHeldForMonths,

D1_NonMotoringConvictions,

D1_MedicalConditions,

D1_AccessToOtherCars,

D1_MainOccGroup,

D1_MainOccRisk,

D1_MainOccNight,

D1_MainOccDrivesForJob,

D1_MainOccMultiplier,

D1_MainOccABICode,

D1_AddOccGroup,

D1_AddOccRisk,

D1_AddOccNight,

D1_AddOccDrivesForJob,

D1_AddOccMultiplier,

D1_AddOccABICode,

D1_DriverAge,

D1_MonthsResidency,

D1_NumClaims,

D1_AtFaultClaims,

D1_YearsClaimFree,

D1_TotFine,

D1_MaxFine,

D1_TotPenaltyPoints,

D1_MaxPenaltyPoints,

D1_NumConvictions,

D1_YearsConvictionFree,

D1_DriverMonthsBanned,

D2_RelationshipToPolicyHolder,

D2_Gender,

D2_DateOfBirth,

D2_MaritalStatus,

D2_MainOccupation,

D2_MainBusinessType,

D2_MainEmploymentStatus,

D2_AdditionalOccupation,

D2_AdditionalBusinessType,

D2_UKResidentSinceBirth,

D2_UKResidentSince,

D2_LicenceType,

D2_LicenceHeldForMonths,

D2_NonMotoringConvictions,

D2_MedicalConditions,

D2_AccessToOtherCars,

D2_MainOccGroup,

D2_MainOccRisk,

D2_MainOccNight,

D2_MainOccDrivesForJob,

D2_MainOccMultiplier,

D2_MainOccABICode,

D2_AddOccGroup,

D2_AddOccRisk,

D2_AddOccNight,

D2_AddOccDrivesForJob,

D2_AddOccMultiplier,

D2_AddOccABICode,

D2_DriverAge,

D2_MonthsResidency,

D2_NumClaims,

D2_AtFaultClaims,

D2_YearsClaimFree,

D2_TotFine,

D2_MaxFine,

D2_TotPenaltyPoints,

D2_MaxPenaltyPoints,

D2_NumConvictions,

D2_YearsConvictionFree,

D2_DriverMonthsBanned,

D3_RelationshipToPolicyHolder,

D3_Gender,

D3_DateOfBirth,

D3_MaritalStatus,

D3_MainOccupation,

D3_MainBusinessType,

D3_MainEmploymentStatus,

D3_AdditionalOccupation,

D3_AdditionalBusinessType,

D3_UKResidentSinceBirth,

D3_UKResidentSince,

D3_LicenceType,

D3_LicenceHeldForMonths,

D3_NonMotoringConvictions,

D3_MedicalConditions,

D3_AccessToOtherCars,

D3_MainOccGroup,

D3_MainOccRisk,

D3_MainOccNight,

D3_MainOccDrivesForJob,

D3_MainOccMultiplier,

D3_MainOccABICode,

D3_AddOccGroup,

D3_AddOccRisk,

D3_AddOccNight,

D3_AddOccDrivesForJob,

D3_AddOccMultiplier,

D3_AddOccABICode,

D3_DriverAge,

D3_MonthsResidency,

D3_NumClaims,

D3_AtFaultClaims,

D3_YearsClaimFree,

D3_TotFine,

D3_MaxFine,

D3_TotPenaltyPoints,

D3_MaxPenaltyPoints,

D3_NumConvictions,

D3_YearsConvictionFree,

D3_DriverMonthsBanned,

D4_RelationshipToPolicyHolder,

D4_Gender,

D4_DateOfBirth,

D4_MaritalStatus,

D4_MainOccupation,

D4_MainBusinessType,

D4_MainEmploymentStatus,

D4_AdditionalOccupation,

D4_AdditionalBusinessType,

D4_UKResidentSinceBirth,

D4_UKResidentSince,

D4_LicenceType,

D4_LicenceHeldForMonths,

D4_NonMotoringConvictions,

D4_MedicalConditions,

D4_AccessToOtherCars,

D4_MainOccGroup,

D4_MainOccRisk,

D4_MainOccNight,

D4_MainOccDrivesForJob,

D4_MainOccMultiplier,

D4_MainOccABICode,

D4_AddOccGroup,

D4_AddOccRisk,

D4_AddOccNight,

D4_AddOccDrivesForJob,

D4_AddOccMultiplier,

D4_AddOccABICode,

D4_DriverAge,

D4_MonthsResidency,

D4_NumClaims,

D4_AtFaultClaims,

D4_YearsClaimFree,

D4_TotFine,

D4_MaxFine,

D4_TotPenaltyPoints,

D4_MaxPenaltyPoints,

D4_NumConvictions,

D4_YearsConvictionFree,

D4_DriverMonthsBanned,

PolicyHolder,

MainDriver,

Num_Drivers,

DrivingRestrictions,

MoreThanClickThrough,

EChoiceClickThrough,

VehModCount

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

So this is my current script, and I would like to be able to change CountClickThroughWholeOfMarket which is (0,1,2..,15) to (0,1,2,3-5,6-10,11+). And if possible it would good if there was some way to change that on the fly, for example split 6-10 in to 6-8 and 9-10.

All I've really done so far is produce graphs and change their properties, haven't really done any 'script coding'. It would be nice if there were a way to achieve this through the graph properties.

Re: Grouping ages into bands

marcus_malinow — Thu, 07 Aug 2014 12:53:23 GMT

OK, first thing. Remove the 'Directory;' statement and replace it with a table name, like so

Data:

Then add the section of my script above, from Intervals onwards.

In the added script, enumerate all your intervals. Also, change the IntervalMatch (MatchedValue) to INtervalMatch (CountClickThroughWholeOfMarket)

If you want dynamic intervals, that's going to be slightly trickier. Would suggest getting this bit working as a starting point.