Interval match possible when a score:rank possibility is dynamic?

[#1]

Dear users,

I'm facing a challenge - with the Interval Match function is it possible to dynamically handle a benchmark file where each individual variable (question) and domain (summary section of variables) score to a ranking which is different for each instance?

Using the below coding each score correctly fell into a "bucket" of ranges for percentile, e.g. score 70.4 fell within "Between 80th Percentile and 90th Percentile", but this isn't as precise as is required.

We need to know specifically when a score falls within each percentile, for example, a score of a 70.4 in one domain is in the 85th percentile, whereas in another domain, the score of 70.4 is in the 70th percentile.

Another issue is whether to embed this coding within the edit script or to have it as an object; the latter seems to allow more flexibility for the end user on the front end.

Much appreciation for your help!

Benchmark:
LOAD PERIOD,
SCORE_TYPE,
SERV_ABBR,
PG_ABBR,
VAR_NAME,
ANALYSIS_ID,
LABEL as Domain,
RANK,
num(SCORE, '##.##%') as SCORE
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE(SCORE_TYPE = 'TOP_BOX' And SERV_ABBR = 'IN CAHPS');

LOAD HCAHPS_VARIABLE,
HCAHPS_VALUE,
PTKEY,
Eff_Response,
Top_Box,
All_Responses,
CMS_Distinction,
Domain,
INSVCCHECK,
QUESTION_TEXT
FROM
[....qvd]
(qvd);

IntervalTest:
LOAD * INLINE [
SCORE_Start, SCORE_End, SCORE_RANKING
90.00, 99.99, '>= 90th Percentile'
80.00, 89.99, 'Between 80th Percentile and 90th Percentile'
70.00, 79.99, 'Between 70th Percentile and 80th Percentile'
60.00, 69.99, 'Between 60th Percentile and 70th Percentile'
0.00, 59.99, '< 60th Percentile'
];

//Link the field RANK to the time intervals defined by the fields Start and End.

Inner Join IntervalMatch (RANK)

LOAD SCORE_Start, SCORE_End
Resident IntervalTest;

[swuehl]

Look into the extended syntax version of Intervalmatch() where you can define keys for the matching, like your domain field.

edit:

IntervalMatch (Extended Syntax) ‒ QlikView

IntervalMatch ‒ QlikView  (see example 2)

[sunny_talwar]

May be use Extended Interval Match for your scenario. But you would need to specify in your inline table which domain has which value falling under what percentile.

IntervalMatch (Extended Syntax) ‒ QlikView‌

Qlikview Tutorial: Extended IntervalMatch

[sunny_talwar]

Basically this is how the script would look (loosely)

Benchmark:
LOAD PERIOD,
SCORE_TYPE,
SERV_ABBR,
PG_ABBR,
VAR_NAME,
ANALYSIS_ID,
LABEL as Domain,
RANK,
num(SCORE, '##.##%') as SCORE
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE(SCORE_TYPE = 'TOP_BOX' And SERV_ABBR = 'IN CAHPS');

LOAD HCAHPS_VARIABLE,
HCAHPS_VALUE,
PTKEY,
Eff_Response,
Top_Box,
All_Responses,
CMS_Distinction,
Domain,
INSVCCHECK,
QUESTION_TEXT
FROM
[....qvd]
(qvd);

IntervalTest:
LOAD * INLINE [
SCORE_Start, SCORE_End, SCORE_RANKING, Domain
90.00, 99.99, '>= 90th Percentile', blah-blah
80.00, 89.99, 'Between 80th Percentile and 90th Percentile', blah-blah
70.00, 79.99, 'Between 70th Percentile and 80th Percentile', blah-blah
60.00, 69.99, 'Between 60th Percentile and 70th Percentile', blah-blah
0.00, 59.99, '< 60th Percentile', blah-blah
];

//Link the field RANK to the time intervals defined by the fields Start and End.

Inner Join IntervalMatch (RANK, Domain)
LOAD SCORE_Start,

          SCORE_End,

          Domain
Resident IntervalTest;

[swuehl]

Re-reading your post, it might be better to calculate the decile the score falls into in the frontend, using fractile() function.

Creating some sample data

LOAD *, 
  Round(NORMINV(RAND(), 100*Sqrt(Domain), 10*Domain)) as Score,
  iterno() as SocreID
WHILE iterno() <=100;  
LOAD dual('Domain '&recno(),recno()) as Domain
AutoGenerate 3;

Then e.g. create a chart with dimensions ScoreID and Domain, and as expression:

=Score &

'  ('&

if(Score <= fractile(TOTAL<Domain> Score, .1), ' 0% to  10%',

if(Score <= fractile(TOTAL<Domain> Score, .2), '10% to  20%',

if(Score <= fractile(TOTAL<Domain> Score, .3), '20% to  30%',

if(Score <= fractile(TOTAL<Domain> Score, .4), '30% to  40%',

if(Score <= fractile(TOTAL<Domain> Score, .5), '40% to  50%',

if(Score <= fractile(TOTAL<Domain> Score, .6), '50% to  60%',

if(Score <= fractile(TOTAL<Domain> Score, .7), '60% to  70%',

if(Score <= fractile(TOTAL<Domain> Score, .8), '70% to  80%',

if(Score <= fractile(TOTAL<Domain> Score, .9), '80% to  90%','90% to 100%')))))))))

&')'

[#6]

Thank you everyone! Trying out these solutions this morning and will update later!

>>> swuehl <qcwebmaster@qlikview.com> 4/6/2016 4:32 PM >>>

Interval match possible when a score:rank possibility is dynamic?

reply from swuehl in Scripting - View the full discussion

Look into the extended syntax version of Intervalmatch() where you can define keys for the matching, like your domain field.

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in Scripting by email or at Qlik Community

Following Interval match possible when a score:rank possibility is dynamic? in these streams: Inbox

© 1993-2016 QlikTech International AB | Copyright & Trademarks | Privacy | Terms of Use | Software EULA

#####################################

This message is for the named person's use only. It may

contain private, proprietary, or legally privileged information.

No privilege is waived or lost by any mistransmission. If you

receive this message in error, please immediately delete it and

all copies of it from your system, destroy any hard copies of it,

and notify the sender. You must not, directly or indirectly, use,

disclose, distribute, print, or copy any part of this message if you

are not the intended recipient. Health First reserves the right to

monitor all e-mail communications through its networks. Any views

or opinions expressed in this message are solely those of the

individual sender, except (1) where the message states such views

or opinions are on behalf of a particular entity; and (2) the sender

is authorized by the entity to give such views or opinions.