IntervalMatch - what am I doing wrong?

[Anonymous]

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_Score	Max_Band_Score	Band_Name
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
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

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

[swuehl]

Try

ScoreForData:

IntervalMatch(MarketMinimumScore, [Measure Type])

Load distinct Min_Band_Score,Max_Band_Score, [Measure Type]

resident Scoring;

[thkarner]

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;

[Anonymous]

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

[thkarner]

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?

[Anonymous]

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):

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

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.

Any ideas what I am doing wrong?

Many thanks for your help once again

Chris

[thkarner]

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

[Anonymous]

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

[thkarner · Accepted solution]

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;