Thank you everyone! Trying out these solutions this morning and will update later!
>>> swuehl <firstname.lastname@example.org> 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
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.
Basically this is how the script would look (loosely)
LABEL as Domain,
num(SCORE, '##.##%') as SCORE
[S:\DSA\Projects\Active\Patient Satisfaction\Mapping Tables\Benchmark Mapping 00.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
WHERE(SCORE_TYPE = 'TOP_BOX' And SERV_ABBR = 'IN CAHPS');
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)
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:
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%')))))))))
comm212370.qvw 165.8 K