12 Replies Latest reply: Aug 7, 2014 6:27 PM by Kaushik Solanki RSS

    Grouping ages into bands

      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

          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
            Deepak Tibhe

            Hi,

             

            You can use class function for this

             

             

            Thanks

            • Re: Grouping ages into bands
              Kaushik Solanki

              HI,

               

              Have a look at the example posted on this post.

               

              Class function | Qlik Community

               

              Regards,

              Kaushik Solanki

              • Re: Grouping ages into bands

                Cheers for the help guys. Class worked just fine.

                Much appreciated.

                • Re: Grouping ages into bands
                  Marco Wedel

                  Custom Filter(unusual range)

                   

                  Maybe also helpful if there would be unequal sized groups.

                   

                  Regards

                   

                  Marco

                  • Re: Grouping ages into bands

                    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

                        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

                            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

                                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

                                    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

                                    [S:\Motor\Pricing\Kristian\quotesall_08.06.2014.csv]

                                    (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

                                        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.