8 Replies Latest reply: Mar 25, 2016 12:41 PM by Thomas Karner RSS

    IntervalMatch - what am I doing wrong?

    Chris Weldon

      Folks,

       

      New ish user to Sense, and trying to get my head round IntervalMatch.

       

      I have a data table (called Data) which has a value called MarketMinimumScore, and also contains a field called MeasureType.  I need to match this up with a table that looks like this:

      MeasureType

      Min_Band_ScoreMax_Band_ScoreBand_Name
      Turnaround1.301000.00Good
      Turnaround1.101.2999Above Minimum
      Turnaround0.901.0999At Minimum
      Turnaround0.700.8999Below Minimum
      Turnaround0.000.6999Poor
      Quality1.10999.99Good
      Quality1.051.0999Above Minimum
      Quality0.951.0499At Minimum
      Quality0.900.9499Below Minimum

      Quality

      0.000.8999Poor

       

       

      So I need to match MarketMinimumScore against Min_Band_Score and Max_Band_Score and then keep the Band_Name somewhere I can use it in conjunction with other things in the Data table to aggregate data and create a chart.

       

      I have looked around on here and the syntax / code I seem to need is

       

      ScoreForData:

      IntervalMatch([Measure Type],MarketMinimumScore)

      Load distinct Min_Band_Score,Max_Band_Score

      resident Scoring;

       

      Which seems to run, but when I look at the Data model nothing seems to have changed from when there was no code (is that right?) and when I try and look at the data in a table there is a count of 1 in each band for each entry in the Data table.

       

      Any ideas what I am doing wrong?  I am sure it is pretty simple!

       

      Many thanks

       

      Chris

        • Re: IntervalMatch - what am I doing wrong?
          Stefan Wühl

          Try

           

          ScoreForData:

          IntervalMatch(MarketMinimumScore, [Measure Type])

          Load distinct Min_Band_Score,Max_Band_Score, [Measure Type]

          resident Scoring;

          • Re: IntervalMatch - what am I doing wrong?
            Thomas Karner

            Hi Chris,

             

            this should solve your problem (if I understood correctly).

            Consider that this works only if decimal separator is . (dot).

             

            If this answer is applicable for you please mark it as helpful or solved.

             

            Regards,

            Thomas

             

            SET ThousandSep=',';

            SET DecimalSep='.';

             

            // MeasureType renamed to MeasureTypeScoring to avoid join

            Scoring:

            LOAD * INLINE [

                MeasureTypeScoring, Min_Band_Score, Max_Band_Score, Band_Name_Scoring

                Quality, 1.10, 999.99, Good

                Quality, 1.05, 1.0999, Above Minimum

                Quality, 0.95, 1.0499, At Minimum

                Quality, 0.90, 0.9499, Below Minimum

                Quality, 0.00, 0.8999, Poor

                Turnaround, 1.30, 1000.00, Good

                Turnaround, 1.10, 1.2999, Above Minimum

                Turnaround, 0.90, 1.0999, At Minimum

                Turnaround, 0.70, 0.8999, Below Minimum

                Turnaround, 0.00, 0.6999, Poor

            ];

             

             

            Data:

            LOAD

              RecNo() AS RecNo,          // example to distinguish different data records

              *;

            LOAD * INLINE [

              MeasureType, MarketMinimumScore

              Quality, 1.08,

              Quality, 0.94,

              Turnaround, 1.15,

              Turnaround, 0.94

            ];

             

            // Join matched intervals to table Data

            LEFT JOIN(Data)

            IntervalMatch(MarketMinimumScore, MeasureType)

            Load

              distinct

              Min_Band_Score,

              Max_Band_Score,

              MeasureTypeScoring AS MeasureType

            Resident Scoring;

             

            // join field Band_Name

            LEFT JOIN(Data)

            LOAD

              Min_Band_Score, // join-field

              Max_Band_Score, // join-field

              MeasureTypeScoring AS MeasureType, // join-field

              Band_Name_Scoring AS Band_Name

            Resident Scoring;

             

            // drop min-/max fields from table Data to avoid join and synthetic keys

            DROP Fields Min_Band_Score, Max_Band_Score from Data;

              • Re: IntervalMatch - what am I doing wrong?
                Chris Weldon

                Hmmmm - closer!

                 

                I think this is still giving one record (identical) for each Band_Name.  So if I only input one record for each category in the Data table, I end up with 5 records after I run this script.

                 

                The other thing is I read somewhere that you shouldn't use Left Joins as when the recordset gets bigger, it can cause problems - not that this matters particularly here.

                 

                Thanks for your help so far.

                 

                Chris

                  • Re: IntervalMatch - what am I doing wrong?
                    Thomas Karner

                    Hi Chris,

                     

                    yes, my script looks for each record in table Data in which bandwith it´s lying in table Scoring with consideration of field MeasureType. In the end the correct Band_Name is identified and joined as additional field in table Data.

                    I thought this is exactly what you want, isn´t it?

                     

                    If not please provide a small XLS sheet with your source tables and some data.

                    Additionally please describe your target data model. This should help to clarify.

                     

                    Regards,

                    Thomas

                     

                    PS: Haven´t heard about your Left Join topic. Have used it with dozens of million records without any problems. Do you have more information?

                      • Re: IntervalMatch - what am I doing wrong?
                        Chris Weldon

                        Thomas,

                         

                        Many thanks for your help so far - I apologise for the delay in replying - I have been travelling with work.

                         

                        To try and troubleshoot, I stripped the relevant parts out into a separate app, but I can't see a way of upload it here.  So I will work with screenshots.

                         

                        So the input code is as follows (a few field name changes since above):

                        Input1.jpgInput2.jpg

                         

                        Input3.jpg

                         

                        But the output has 2 problems I think.  Firstly I have some records that seem to be responding to the input code itself:

                        Output1.jpg

                         

                        And secondly I al getting multiple records for a single input row.  So for example the input file only has one record for Quality of Quote, but the table has 5 - one for each Band_Scoring_Name.

                        Output2.jpg

                        Any ideas what I am doing wrong?

                         

                        Many thanks for your help once again

                         

                        Chris

                          • Re: IntervalMatch - what am I doing wrong?
                            Thomas Karner

                            Hi Chris, sorry, too much text to re-enter. Can we share the QVF file somehow? (thomas.karner@plaut.com).

                            Otherwise please edit your message and replace pictures with source code for copy / paste.

                            Thanks.

                            Thomas

                              • Re: IntervalMatch - what am I doing wrong?
                                Chris Weldon

                                Thomas,

                                 

                                Here is the full script for the load

                                BandScoringOrder:
                                load * inline [Band_Scoring_Name, Band_Name_Scoring_Category_Order
                                Poor,1
                                Below Minimum,2
                                At Minimum,3
                                Above Minimum,4
                                Good,5
                                ];

                                BandScoring:
                                Load * inline [Measure_Type_Scoring,Min_Band_Score,Max_Band_Score,Band_Scoring_Name
                                Turnaround,1.30,1000,Poor
                                Turnaround,1.10,1.2999, Below Minimum
                                Turnaround,0.9,1.0999,At Minimum
                                Turnaround,0.7,0.8999,Above Minimum
                                Turnaround,0,0.6999,Good
                                Quality,0,0.899,Poor
                                Quality,0.9,0.9499, "Below Minimum"
                                Quality,0.95,1.0499,At Minimum
                                Quality,1.05,1.0999,Above Minimum
                                Quality,1.1,999,Good
                                ];

                                CategoryOrder:
                                Load * inline [Category, Category_Order
                                Quote,1
                                Implementation,2
                                Medical Underwriting,3
                                Policy Administration,4
                                Renewal,5
                                Claims,6
                                Complaints,7
                                ];


                                CountryData:
                                Load * inline [Category,Measure Type,Measure,Importance,Market Minimum,Market Expectation,Company
                                Quote,Turnaround,New BusinessQuoteTurnaroundTime(workingdays),4,10,10,10
                                Quote,Turnaround,RenewalQuoteTurnaroundTime(workingdays),4,10,12,10
                                Quote,Quality,QualityofQuotes,4,0.9,0.75,0.9
                                Implementation,Turnaround,Confirmationofrequirements(workingdays),4,5,5,5
                                Implementation,Turnaround,Time to On-Risk (working days),4,1,1,1
                                Implementation,Turnaround,Time to Invoice First Premium (working days),4,5,5,5
                                Implementation,Turnaround,Time to Issue Policy Documents (working days),4,30,15,45
                                Implementation,Turnaround,Time from Issue to Commission Payment (working days)4,20,20,20
                                Implementation,Quality,Quality of Implementation,4,0.9,0.7,0.85
                                Medical Underwriting,Turnaround,Notifiaction of MU Requirements Turnaround Time (working days),4,5,5,5
                                Medical Underwriting,Turnaround,Decision Turnaround Time (working days),4,5,10
                                Medical Underwriting,Quality,Quality of Medical Underwriting,4,0.99,0.99
                                Policy Administration,Turnaround,Time for Simple Policy Changes (working days),4,5,5,2
                                Policy Administration,Turnaround,Time for Complex Policy Changes (working days),4,15,15,10
                                Policy Administration,Turnaround,Time taken to invoice ongoing premiums,4,20,20,20
                                Policy Administration,Quality,Quality of Policy Administration,4,0.95,0.77,0.87
                                Renewal,Turnaround,Time taken from data received to offer (working days),4,5,5,5
                                Renewal,Turnaround,Time to Invoice Renewal Premium (working days),4,5,5,5
                                Renewal,Turnaround,Time taken from offer to issue documents (working days),4,30,15,45
                                Renewal,Quality,Quality for renewal,4,0.9,0.7,0.85
                                Claims,Turnaround,Notifiaction of Claims Requirements Turnaround Time (working days),4,10,10,
                                Claims,Turnaround,Claim Decision (Life) turnaround,4,5,5,
                                Claims,Turnaround,Claim Decision (Disability) turnaround,4,5,5,,
                                Claims,Turnaround,Claim Payment Turnaround,,,,
                                Claims,Quality,Quality for Claims,4,1,1,
                                Complaints,Turnaround,Acknowledgement of compalint,4,5,5,
                                Complaints,Turnaround,Complaint resolved,,,,

                                // Join matched intervals to table Data
                                LEFT JOIN(CountryData)
                                IntervalMatch(Company/[Market Minimum], MeasureType)
                                Load
                                  distinct
                                  Min_Band_Score,
                                  Max_Band_Score,
                                  Measure_Type_Scoring AS MeasureType
                                Resident BandScoring;

                                // join field Band_Name
                                LEFT JOIN(CountryData)
                                LOAD
                                  Min_Band_Score, // join-field
                                  Max_Band_Score, // join-field
                                  Measure_Type_Scoring AS MeasureType, // join-field
                                  Band_Scoring_Name AS Band_Name
                                Resident BandScoring;

                                // drop min-/max fields from table Data to avoid join and synthetic keys
                                DROP Fields Min_Band_Score, Max_Band_Score from CountryData;

                                 

                                Many thanks for your assistance.

                                 

                                Chris

                                  • Re: IntervalMatch - what am I doing wrong?
                                    Thomas Karner

                                    Hi Chris,

                                    this should solve your issue.

                                    KR Thomas

                                     

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

                                    SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

                                    SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

                                    SET FirstWeekDay=0;

                                    SET BrokenWeeks=1;

                                    SET ReferenceDay=0;

                                    SET FirstMonthOfYear=1;

                                    SET CollationLocale='en-GB';

                                     

                                     

                                    BandScoringOrder:

                                    load * inline [

                                    Band_Scoring_Name, Band_Name_Scoring_Category_Order

                                    Poor,1

                                    Below Minimum,2

                                    At Minimum,3

                                    Above Minimum,4

                                    Good,5

                                    ];

                                     

                                     

                                    BandScoring:

                                    Load * inline [

                                    Measure_Type_Scoring,Min_Band_Score,Max_Band_Score,Band_Scoring_Name

                                    Turnaround,1.30,1000,Poor

                                    Turnaround,1.10,1.2999, Below Minimum

                                    Turnaround,0.9,1.0999,At Minimum

                                    Turnaround,0.7,0.8999,Above Minimum

                                    Turnaround,0,0.6999,Good

                                    Quality,0,0.899,Poor

                                    Quality,0.9,0.9499, "Below Minimum"

                                    Quality,0.95,1.0499,At Minimum

                                    Quality,1.05,1.0999,Above Minimum

                                    Quality,1.1,999,Good

                                    ];

                                     

                                     

                                    CategoryOrder:

                                    Load * inline [

                                    Category, Category_Order

                                    Quote,1

                                    Implementation,2

                                    Medical Underwriting,3

                                    Policy Administration,4

                                    Renewal,5

                                    Claims,6

                                    Complaints,7

                                    ];

                                     

                                    CountryData:

                                    LOAD * inline [

                                    Category,MeasureType,Measure,Importance,Market Minimum,Market Expectation,Company

                                    Quote,Turnaround,New BusinessQuoteTurnaroundTime(workingdays),4,10,10,10

                                    Quote,Turnaround,RenewalQuoteTurnaroundTime(workingdays),4,10,12,10

                                    Quote,Quality,QualityofQuotes,4,0.9,0.75,0.9

                                    Implementation,Turnaround,Confirmationofrequirements(workingdays),4,5,5,5

                                    Implementation,Turnaround,Time to On-Risk (working days),4,1,1,1

                                    Implementation,Turnaround,Time to Invoice First Premium (working days),4,5,5,5

                                    Implementation,Turnaround,Time to Issue Policy Documents (working days),4,30,15,45

                                    Implementation,Turnaround,Time from Issue to Commission Payment (working days)4,20,20,20

                                    Implementation,Quality,Quality of Implementation,4,0.9,0.7,0.85

                                    Medical Underwriting,Turnaround,Notifiaction of MU Requirements Turnaround Time (working days),4,5,5,5

                                    Medical Underwriting,Turnaround,Decision Turnaround Time (working days),4,5,10

                                    Medical Underwriting,Quality,Quality of Medical Underwriting,4,0.99,0.99

                                    Policy Administration,Turnaround,Time for Simple Policy Changes (working days),4,5,5,2

                                    Policy Administration,Turnaround,Time for Complex Policy Changes (working days),4,15,15,10

                                    Policy Administration,Turnaround,Time taken to invoice ongoing premiums,4,20,20,20

                                    Policy Administration,Quality,Quality of Policy Administration,4,0.95,0.77,0.87

                                    Renewal,Turnaround,Time taken from data received to offer (working days),4,5,5,5

                                    Renewal,Turnaround,Time to Invoice Renewal Premium (working days),4,5,5,5

                                    Renewal,Turnaround,Time taken from offer to issue documents (working days),4,30,15,45

                                    Renewal,Quality,Quality for renewal,4,0.9,0.7,0.85

                                    Claims,Turnaround,Notifiaction of Claims Requirements Turnaround Time (working days),4,10,10,

                                    Claims,Turnaround,Claim Decision (Life) turnaround,4,5,5,

                                    Claims,Turnaround,Claim Decision (Disability) turnaround,4,5,5,,

                                    Claims,Turnaround,Claim Payment Turnaround,,,,

                                    Claims,Quality,Quality for Claims,4,1,1,

                                    Complaints,Turnaround,Acknowledgement of compalint,4,5,5,

                                    Complaints,Turnaround,Complaint resolved,,,,

                                    ];

                                     

                                     

                                    // Join matched intervals to table Data

                                    LEFT JOIN(CountryData)

                                    IntervalMatch([Market Minimum], MeasureType)

                                    Load

                                    distinct

                                    Min_Band_Score,

                                    Max_Band_Score,

                                    Measure_Type_Scoring AS MeasureType

                                    Resident BandScoring;

                                     

                                     

                                    // join field Band_Name

                                    LEFT JOIN(CountryData)

                                    LOAD

                                    Min_Band_Score, // join-field

                                    Max_Band_Score, // join-field

                                    Measure_Type_Scoring AS MeasureType, // join-field

                                    Band_Scoring_Name AS Band_Name

                                    Resident BandScoring;

                                     

                                     

                                    // drop min-/max fields from table Data to avoid join and synthetic keys

                                    DROP Fields Min_Band_Score, Max_Band_Score from CountryData;