Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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;

5 Replies
MVP
MVP

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

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)

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

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

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

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;

MVP
MVP

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

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%')))))))))

&')'

2016-04-07 00_23_28-QlikView x64 - [C__Users_Stefan_Downloads_comm212370.qvw_].png

Not applicable

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

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.

Community Browser