Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Try
ScoreForData:
IntervalMatch(MarketMinimumScore, [Measure Type])
Load distinct Min_Band_Score,Max_Band_Score, [Measure Type]
resident Scoring;
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;
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
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?
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
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
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
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;